Grouping multiple records into one row

Question

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

Answer 1

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.