CODEWITHSUNDEEP
mysqlsql
user7724540
user7724540

Reputation: 61

Results not being grouped when using GROUP BY on UNION ALL

I have two tables: Ambulances and Responders. The Responder table has one call_sign column and the Ambulances table has 4 columns - call_sign_1, call_sign_2, call_sign_3, call_sign_4.

I want to write a query to list every call_sign and how many times it appears in the database (i.e. how many calls each responder has attended - including the ones in ambulances). The query I've created displays some call_sign's more than once.

I'd like them to be grouped so each call_sign only appears once.

SELECT * FROM (
select call_sign as call_sign, count(call_sign) as `call_sign_count` from responders group by call_sign
UNION ALL
select call_sign_1 as call_sign, count(call_sign_1) as `call_sign_count` from ambulances group by call_sign
UNION ALL
select call_sign_2 as call_sign, count(call_sign_2) as `call_sign_count` from ambulances group by call_sign
UNION ALL
select call_sign_3 as call_sign, count(call_sign_3) as `call_sign_count` from ambulances group by call_sign
UNION ALL
select call_sign_4 as call_sign, count(call_sign_4) as `call_sign_count` from ambulances group by call_sign
) 
a WHERE call_sign IS NOT NULL Group By call_sign, call_sign_count

The databse and query can be found in this fiddle:

https://www.db-fiddle.com/f/9grxeiA277KtUqsiUbYMA8/0

Upvotes: 1

Views: 79

Answers (1)

Shadow
Shadow

Reputation: 34232

Yes, it displays the same call_sign multiple times because in the outer query you group by on call_sign and call_sign_count. If you want each call_sign to appear only once, then you can only group on that field and use an aggregate function on call_sign_count, let's say sum().

SELECT call_sign, sum(call_sign_count) total_call_sign_count FROM (
select call_sign as call_sign, count(call_sign) as `call_sign_count` from responders group by call_sign
UNION ALL
select call_sign_1 as call_sign, count(call_sign_1) as `call_sign_count` from ambulances group by call_sign
UNION ALL
select call_sign_2 as call_sign, count(call_sign_2) as `call_sign_count` from ambulances group by call_sign
UNION ALL
select call_sign_3 as call_sign, count(call_sign_3) as `call_sign_count` from ambulances group by call_sign
UNION ALL
select call_sign_4 as call_sign, count(call_sign_4) as `call_sign_count` from ambulances group by call_sign
) 
a WHERE call_sign IS NOT NULL Group By call_sign

Upvotes: 2

Related Questions