將多條記錄分組為一行

我需要獲取應用程序的每日報告。

一份報告應該告訴我一個活動頁面被訪問了多少次，以及每個類別售出了多少張門票。所有這些都應該按天分組在一行中，如下所示：

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

使用序列表，我可以按天對結果進行分組，但我能做的最好的事情是每天為每個類別設置一行，即，如果我有 3 個類別，我每天得到 3 行.

小提琴

如果類別的數量是預先已知的，這只是對現有解決方案的一些條件求和visits……並通過子查詢添加所需的任何邏輯：

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 ;

你會得到：

|       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 |

小提琴。

引用自：https://dba.stackexchange.com/questions/169148