2

I need to get daily reports for an app.

A report should tell me how many times a event page was visited and how many tickets were sold for each category. All that should be grouped by day in a single row, like so:

Date        | Category A | Category B | Category C | Visits
'2017-03-10'|         10 |          2 |          8 |     30

Using a sequence table I was able to group results by day, but the best I've been able to do is to have a row for each category for each day, ie, if I have 3 categories, I get 3 rows for each day.

Fiddle

3
  • Is the number of categories fixed and known in advance?
    – joanolo
    Commented Apr 4, 2017 at 23:51
  • @joanolo No, it's not. Unless I do another query beforehand to get a list of categories for the event.
    – rzb
    Commented Apr 4, 2017 at 23:53
  • 2
    Then there isn't any "automatic" way of doing it... because the column list of a query must be known beforehand. See answer. Other databases would allow you to do that using PIVOT, but not MySQL.
    – joanolo
    Commented Apr 4, 2017 at 23:59

1 Answer 1

3

If the number of categories is fixed an known in advance, this is just some conditional summing over your existing solution... and add whatever logic is needed for the visits via a subquery:

SELECT
    date,
    sum(CASE WHEN `categoryId`=1 THEN `ticketCount` END) AS `Category A`,
    sum(CASE WHEN `categoryId`=2 THEN `ticketCount` END) AS `Category B`,
    sum(CASE WHEN `categoryId`=3 THEN `ticketCount` END) AS `Category C`,
    (SELECT  count(id) FROM event_page_visits 
      WHERE event_id = 1 and date(created_at) = date) AS `Visits`
FROM
(
    select 
      `list_of_dates`.`date` as `date`, `ticket_categories`.`id` as `categoryId`, `ticket_categories`.`name` as `categoryName`, count(tickets.id) as ticketCount 
    from ( 
      SELECT DATE_ADD('2017-03-10', INTERVAL seq.seq DAY) AS DATE FROM seq_0_to_999 AS seq WHERE DATE_ADD('2017-03-10', INTERVAL seq.seq DAY) <= '2017-03-14' 
    ) as list_of_dates 
    cross join 
      `ticket_categories` 
    left join 
      `tickets` on 
        `tickets`.`ticket_category_id` = `ticket_categories`.`id` and 
        `tickets`.`created_at` >= `list_of_dates`.`date` and 
        `tickets`.`created_at` < list_of_dates.date + INTERVAL 1 DAY
    where 
      `ticket_categories`.`event_id` = 1
    group by 
      `date`, `ticket_categories`.`id`
) AS q
GROUP BY 
   `date`
order by 
  `date` asc ;

And you just get:

|       date | Category A | Category B | Category C | Visits |
|------------|------------|------------|------------|--------|
| 2017-03-10 |          4 |          3 |          2 |     10 |
| 2017-03-11 |          0 |          0 |          0 |      2 |
| 2017-03-12 |          1 |          1 |          0 |      3 |
| 2017-03-13 |          0 |          0 |          3 |      6 |
| 2017-03-14 |          0 |          0 |          0 |      0 |

Fiddle.

3
  • Thanks. I'm ok with an extra query to get the list of categories. What about Visits? It's not the total ticketCount, it comes from another table entirely. Trying to join it in your "master" query gave me crazy results.
    – rzb
    Commented Apr 5, 2017 at 0:44
  • I've fixed the visit count by adding a subquery to the selection: fiddle.
    – rzb
    Commented Apr 5, 2017 at 4:56
  • 1
    Glad it helped. I completely misunderstood vitis... based on the example, I just summed...
    – joanolo
    Commented Apr 5, 2017 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.