SQLITE help

Dev Shed Forums Sponsor:

January 27th, 2012, 09:33 AM

dlopez

Contributing User

Join Date: Oct 2011

Posts: 64

Time spent in forums: 13 h 33 m 11 sec
Reputation Power: 3

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

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,370

Time spent in forums: 3 Months 1 Week 2 Days 6 h 21 m 40 sec
Reputation Power: 4140

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Last edited by r937 : January 27th, 2012 at 10:29 AM.

January 27th, 2012, 10:44 AM