Mysql – Grouping multiple records into one row

group byjoin;MySQLpivot

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

Best Answer

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.

Related Question