mysql - MYSQL中如何基于一列合并值

Question

我正在运行此查询并获取如下附件中的数据

mysql> ( SELECT date_range AS 'Time Elapsed', COUNT(*) AS 'Conversions' FROM `data` GROUP BY date_sort_sno, `date_range` ORDER BY date_sort_sno )
    ->     UNION ALL
    -> ( SELECT date_range AS 'Time Elapsed', COUNT(*) AS 'A_Conversio' FROM `data` GROUP BY date_sort_sno, `date_range` WHERE name = 'Alpha' ORDER BY date_sort_sno );

输出

+------------------------------+-------------+
| Time Elapsed                 | Conversions |
+------------------------------+-------------+
| Less than equal to 5 minutes |        1391 |
| 5 to 30 minutes              |        9108 |
| 30 to 60 minutes             |        2233 |
| 1 hour to 3 hours            |        2280 |
| 3 hours to 24 hours          |        4585 |
| 1 to 3 days                  |        4143 |
| 3 to 6 days                  |         155 |
| more than 6 days             |          80 |
| Less than equal to 5 minutes |        1391 |
| 5 to 30 minutes              |        9108 |
| 30 to 60 minutes             |        2233 |
| 1 hour to 3 hours            |        2280 |
| 3 hours to 24 hours          |        4585 |
| 1 to 3 days                  |        4143 |
| 3 to 6 days                  |         155 |
| more than 6 days             |          80 |
+------------------------------+-------------+

问题陈述:我想像 Time Elapsed 一样合并数据

+------------------------------+-------------+-------------+
| Time Elapsed                 | Conversions | A_Conversio |
+------------------------------+-------------+-------------+
| Less than equal to 5 minutes |        1391 |        1231 |
| 5 to 30 minutes              |        1391 |        4455 |
| 30 to 60 minutes             |        2233 |        3333 |
| 1 hour to 3 hours            |        2280 |        4343 |
| 3 hours to 24 hours          |        4585 |        2234 |
| 1 to 3 days                  |        4143 |        2344 |
| 3 to 6 days                  |         155 |         455 |
| more than 6 days             |          80 |          11 |
+------------------------------+-------------+-------------+

Answer 1

您可以有一个包含两列的查询，而不是两个单独的查询 - 一个普通的旧 count(*) 用于计算所有记录，一个 count 函数应用于一个 case 表达式，仅计算名称为 'Alpha' 的转换:

SELECT   date_range AS 'Time Elapsed', 
         COUNT(*) AS 'Conversions',
         COUNT(CASE name WHEN 'Alpha' THEN 1 END) AS 'A_Conversio'
FROM     `data`
GROUP BY date_sort_sno, `date_range`
ORDER BY date_sort_sno