[dlopez]

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().

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!

[r937]

Originally Posted by dlopez

I'm not familiar with the functions in Sqlite.

http://www.sqlite.org/lang_corefunc.html

SELECT STRFTIME('%m',created_at) AS mm
     , COUNT(*)
  FROM tickets
 WHERE created_at >= DATE('now','start of year')
GROUP
    BY mm

[dlopez]

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.

[r937]

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