I have two queries:
select place, count(*) from tableA group by place Place A 2 Place B 3 Place C 5 select count(place) from tableA 10
These return a list of places and their count, and a total count for all places. Is there a way to do this in one statement?
Yes. Combine them with UNION ALL. Note that your first query returns two columns, place and count, whereas your second returns only one, the count. Therefore we need to use a "placeholder" value in the second query. This is because each query in the union must return the same number of columns. But that's okay, because the placeholder we will use is the literal ' Total' which will help identify what the count is.
select place , count(*) as places from tableA group by place union all select ' Total' , count(*) from tableA order by case when place=' Total' then 1 else 0 end , place Place A 2 Place B 3 Place C 5 Total 10
You can clearly see what each of the two queries is doing. A simple UNION query. Somewhat bulky, but straightforward. Okay, except maybe for the ORDER BY.
In a union, the ORDER BY might look like it's part of the last query of the union, but it actually operates on the entire combined result sets of all queries in the union. So what does the CASE expression do in the ORDER BY above? It ensures that the "Total" line in the result set sorts last.
Check to see whether your database supports the WITH ROLLUP option, as Microsoft SQL Server does:
select place , count(*) as places from tableA group by place with rollup Place A 2 Place B 3 Place C 5 NULL 10
Lovely syntax, very intuitive. To make the NULL say 'Total' requires a small adjustment:
select coalesce(place,' Total') as theplace , count(*) as places from tableA group by place with rollup order by case when place is null then 1 else 0 end , place Place A 2 Place B 3 Place C 5 Total 10
The COALESCE catches the NULL produced by the ROLLUP.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.