mysql - mysql中特定条件的总和

Question

查询:

select date, query_type, light_mapped_count, heavy_mapped_count, lightandheavy_mapped_count, total_queries
from search_converted_play

我得到:

| date       | query_type     | light_mapped_count | heavy_mapped_count |
| 2015-01-01 | browse         |                162 |                592 |
| 2015-01-01 | browse_scroll  |                 11 |                 48 |
| 2015-01-02 | browse         |                640 |               3001 |                        253 |
| 2015-01-02 | browse_scroll  |                 75 |                570 |                         49 |

现在，我想要特定日期的 light_mapped_count 和 Heavy_mapped_count 的所有查询类型的总和，因此输出应该是:

| date       | query_type     | light_mapped_count | heavy_mapped_count |
| 2015-01-01 | browse         |                162 |                592 |
| 2015-01-01 | browse_scroll  |                 11 |                 48 |
| 2015-01-01 | all            |                173 |                640 |
| 2015-01-02 | browse         |                640 |               3001 |                        253 |
| 2015-01-02 | browse_scroll  |                 75 |                570 |                         49 |
| 2015-01-02 | all            |                715 |               3571 |

知道如何做到这一点吗？

Answer 1

尝试使用 UNION，如下所示:

SELECT date, query_type, light_mapped_count, heavy_mapped_count
FROM search_converted_play
UNION
SELECT date, 'all', SUM(light_mapped_count), SUM(heavy_mapped_count)
FROM search_converted_play
GROUP BY date
ORDER BY date