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

  • Is the number of categories fixed and known in advance? – joanolo Apr 4 '17 at 23:51
  • @joanolo No, it's not. Unless I do another query beforehand to get a list of categories for the event. – rzb Apr 4 '17 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 Apr 4 '17 at 23:59
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.

  • 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 Apr 5 '17 at 0:44
  • I've fixed the visit count by adding a subquery to the selection: fiddle. – rzb Apr 5 '17 at 4:56
  • Glad it helped. I completely misunderstood vitis... based on the example, I just summed... – joanolo Apr 5 '17 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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