Mysql combine count query into single column from multiple columns

Question

I have a table 'Details' as below

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

I need the total number of rows sorted based service and location

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

which gives result as

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

I need result as

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

Fahmi · Accepted Answer · 2020-08-20 04:45:34Z

2

You can try the below using conditional aggregation

DEMO

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

edited Aug 20, 2020 at 4:45

answered Aug 20, 2020 at 3:40

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hari Prasandh Over a year ago

What is 'total' in case expression, Table Details doesn't have a column as total ?

Fahmi Over a year ago

@HariPrasandh, check now

Hari Prasandh Over a year ago

Query is executed, Total is fetched correctly but case expression is not working as intended, it just places '1' instead of actual count

Fahmi Over a year ago

@HariPrasandh, check here dbfiddle.uk/…

Hari Prasandh Over a year ago

Works. I missed changing max() to count() in select, when the query was changed

Collectives™ on Stack Overflow

Mysql combine count query into single column from multiple columns

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related