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