Thread: SQLITE help

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    64
    Rep Power
    4

    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
    (mmcreated_at) as date
    FROM
         tickets
    WHERE DateDiff
    (yyyycreated_atGetDate()) = 0
    Group by DatePart
    (mm,created_at)
    Order by DatePart(mmcreated_at
    Could anyone help me out with what syntax that I would need to accomplish this?

    Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    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.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2011
    Posts
    64
    Rep Power
    4
    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.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo