January 27th, 2012, 09:33 AM
-
SQLITE help
Hello,
I'm building dashboard in php, retrieving data from multiple database types. Right now, my focus is on a Spiceworks SQLITE database.
I'm not familiar with the functions in Sqlite. I'm trying to query the monthly ticket total, group the total by month and store that in an array. I'm not for sure about the SQL syntax to do this.... I could just select * from the table and have the php process the data, but I would rather the data be processes when the query executes. Right now, I'm using something that has DATEPART().
PHP Code:
SELECT
DatePart(mm, created_at) as date
FROM
tickets
WHERE DateDiff(yyyy, created_at, GetDate()) = 0
Group by DatePart(mm,created_at)
Order by DatePart(mm, created_at)
Could anyone help me out with what syntax that I would need to accomplish this?
Thanks!
January 27th, 2012, 10:26 AM
-
Originally Posted by dlopez
I'm not familiar with the functions in Sqlite.
http://www.sqlite.org/lang_corefunc.html
Code:
SELECT STRFTIME('%m',created_at) AS mm
, COUNT(*)
FROM tickets
WHERE created_at >= DATE('now','start of year')
GROUP
BY mm
Last edited by r937; January 27th, 2012 at 10:29 AM.
January 27th, 2012, 10:44 AM
-
Thanks for that. I'm needing to count the total for each individual month, however. I added ... ,COUNT(*) as count FROM tickets... etc. so total number of 08, 09, 10, etc.
Should I do the actual sorting on the PHP side? Or does SQLITE have a way of doing this? I'm not an SQL expert, that's for sure.
January 27th, 2012, 11:00 AM
-
Originally Posted by dlopez
Should I do the actual sorting on the PHP side? Or does SQLITE have a way of doing this?
use ORDER BY
i left it out until i heard back that GROUP BY mm worked