mysql - Mysql将计数查询从多列组合成单列

Question

我有一个表格“详细信息”，如下所示

| ID | NAME   | PARTS | SERVICE | LOCATION | TIME          |
|----|--------|-------|---------|----------|---------------|
| 1  | John   | 5     | Repair  | A        | 1597893635294 |
| 2  | Smith  | 1     | Install | A        | 1597893635294 |
| 3  | Will   | 1     | Repair  | B        | 1597893635294 |
| 4  | Jade   | 10    | Install | A        | 1597893635294 |
| 5  | George | 2     | Install | B        | 1597893635294 |
| 6  | Ray    | 4     | Repair  | A        | 1597893635294 |

我需要基于服务和位置排序的总行数

Select SERVICE,LOCATION,count(*) as TOTAL from Details group by SERVICE,LOCATION order by SERVICE desc;

结果为

| SERVICE | LOCATION | TOTAL |
|---------|----------|-------|
| Repair  | A        | 2     |
| Repair  | B        | 1     |
| Install | A        | 2     |
| Install | B        | 1     |

我需要结果

| SERVICE | A | B | TOTAL |
|---------|---|---|-------|
| Repair  | 2 | 1 | 3     |
| Install | 2 | 1 | 3     |

标签： mysqlsql

Answer 1

您可以尝试以下使用conditional aggregation

演示

Select SERVICE,
       count(case when location='A' then 1 end) as A,
       count(case when location='B' then 1 end) as B,
       count(*) as TOTAL 
from Details group by SERVICE
order by SERVICE desc