Skip to content
Advertisement

How to sum COUNT values

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement