I’m very new to SQLite so please forgive me asking something stupid. From a database with a table containing aircraft data I can count the records with data from a manufacturer with my existing query:
x
SELECT Manufacturer, COUNT(Manufacturer) AS Number
FROM aircraft
WHERE Manufacturer like 'Airbus%'
GROUP BY Manufacturer
ORDER BY COUNT(Manufacturer) DESC;
This gives me
Manufacturer Number
Airbus 3473
Airbus Military 29
Airbus Helicopters 2
Is there a way to get the calculated grand total (3504) in the results without removing the ‘GROUP BY’clause? I can’t get SUM() and TOTAL() to work. The questions about this counting an aggregating seem to use values found in the tables.
Advertisement
Answer
With UNION ALL
:
SELECT t.*
FROM (
SELECT Manufacturer, COUNT(Manufacturer) AS Number
FROM aircraft
WHERE Manufacturer like 'Airbus%'
GROUP BY Manufacturer
UNION ALL
SELECT 'Total', COUNT(Manufacturer)
FROM aircraft
WHERE Manufacturer like 'Airbus%'
) t
ORDER BY Manufacturer = 'Total', Number DESC
See the demo.
Or with a CTE:
WITH cte AS (
SELECT Manufacturer, COUNT(Manufacturer) AS Number
FROM aircraft
WHERE Manufacturer like 'Airbus%'
GROUP BY Manufacturer
)
SELECT t.*
FROM (
SELECT * FROM cte
UNION ALL
SELECT 'Total', SUM(Number) FROM cte
) t
ORDER BY MAnufacturer = 'Total', Number DESC
See the demo.