How can I average number of items in our SQLite database by 24-hour period?

Trying to create a report for our support ticketing system and I'm trying to have 2 results in the report that show a rolling average of how many tickets were opened in a day and how many were closed in a day.

Basically, query the entire tickets table, separate out everything by individual days that the tickets were created on, count the number tickets for each individual day, then average that number.

My friend gave me th开发者_JAVA技巧is query:

SELECT AVG(ticket_count)
FROM (SELECT COUNT(*) AS ticket_count FROM tickets
GROUP BY DATE(created_at, '%Y'), DATE(created_at, '%m'), DATE(created_at, '%d')) AS ticket_part

But it's not seeming to work for me. All I get is a single result with the number of tickets created last year.

Here's what finally worked for me:

SELECT round(CAST(AVG(TicketsOpened) AS REAL), 1) as DailyOpenAvg
FROM
(SELECT date(created_at) as Day, COUNT(*) as TicketsOpened
FROM tickets
GROUP BY date(created_at)
) AS X

The middle part of your query is collapsing the table to a single row, so the outer part has nothing upon which to group. It's hard to say exactly what you need without seeing the schema for ticket_count, but at a guess I'd try this:

SELECT
  AVG(CAST(TicketsOpened AS REAL))  -- The cast to REAL ensures that { 1, 2 } averages to 1.5 rather than 1
FROM
  (
  SELECT
    CAST(created_at AS DATE) AS Day  -- The cast to DATE truncates any time element; if you're storing date alone, you can omit this
    COUNT(*) AS TicketsOpened
  FROM
    ticket_count
  GROUP BY
    CAST(created_at AS DATE)
  ) AS X

Hope that helps!