如何在SQL中有效地计算某些列值的出现次数？

I am using MySQL.

我正在使用MySQL。

I have a table of service_status:

我有一个service_status表：

   id | service_id | service_status
   ----------------------------------------------------------------------
   0  | 1001       | download_started
   1  | 1001       | download_started
   2  | 1002       | download_started
   3  | 1002       | download_started
   4  | 1002       | download_failed
   5  | 1003       | download_started
   6  | 1003       | download_failed
   7  | 1003       | something_else
   8  | 1003       | another_thing

I want to query for all service_ids, and two additional columns that count the number of download_started and number of download_failed:

我想查询所有service_ids，以及另外两个列，它们计算download_started的数量和download_failed的数量：

id | service id | download_started | download_failed
----------------------------------------------------------------------
0  | 1001       | 2                | 0
1  | 1002       | 2                | 1
2  | 1003       | 1                | 1

I only care about the statuses that are download_started or download_failed.

我只关心download_started或download_failed的状态。

Thanks a lot.

非常感谢。

2 个解决方案

#1

You could use this:

你可以用这个：

SELECT 
    service_id,
    SUM(CASE WHEN service_status = 'download_started' THEN 1 ELSE 0 END) download_started,
    SUM(CASE WHEN service_status = 'download_failed' THEN 1 ELSE 0 END) download_failed
FROM 
    table_name
GROUP BY 
    service_id
ORDER BY 
    service_id;

#2

In MySQL, you can simplify the expression to:

在MySQL中，您可以将表达式简化为：

SELECT service_id,
       SUM(service_status = 'download_started') as download_started,
       SUM(service_status = 'download_failed') as download_failed
FROM t
GROUP BY service_id;

#1