SQL - Multiple Combination Aggregates

SQL - Structured Query Language (commonly pronounced 'sequel') is arguably the best tool for analysis of data sets from a few items to billions of item of data. If this is news to you, then the rest of this page is likely to be way over your head for the time being, because only those with a reasonable working knowledge of SQL are likely to find this useful - but Google is your friend, and can take to millions of websites where you can learn the basics.

Assuming you have the basics, and understand this

SELECT COUNT(id) AS AllCars FROM motor_fleet;

If you understand the SELECT and COUNT statements in SQL, you'll have worked out that this query will count the number of records in the table `motor_fleet`, and return that number labelled AllCars. This is pretty simple.

One might also want to make selective queries, for example to look at makes, types of power, or how old the cars are:-

SELECT COUNT(*) AS Fords FROM motor_fleet WHERE `make` = 'Ford';
SELECT COUNT(*) AS OldCar FROM motor_fleet WHERE `year_first_reg` < 2018;
SELECT COUNT(*) AS Petrol FROM motor_fleet WHERE `power` = 'Petrol';

This is kind of OK, but running the queries one by one, gives separate answers, and some databases like LibreOffice Base for example, only allow one SQL query at a time, and we haven't started on combined queries yet.

SELECT COUNT(*)  AS OldCar_Fords FROM motor_fleet
    WHERE `make` = 'Ford' AND `year_first_reg` < 2018;
SELECT COUNT(*)  AS Petrol_Fords FROM motor_fleet
    WHERE `make` = 'Ford' AND  `power` = 'Petrol' ;
SELECT COUNT(*)  AS OldCar_Petrol FROM motor_fleet
    WHERE `year_first_reg` < 2018 AND `power` = 'Petrol' ;
SELECT COUNT(*)  AS OldCar_Petrol_Fords FROM motor_fleet
    WHERE `year_first_reg` < 2018 AND `make` = 'Ford' AND `power` = 'Petrol';

What a mess!! Now we have to run four queries, and then manually collate the results to form a single dataset. There is a better way..........

SELECT COUNT(*)  AS AllCars,
		 COUNT(Fords) AS Fords,
		 COUNT(OldCar) AS OldCar,
		 COUNT(Petrol) AS Petrol,
		 COUNT(Fords + OldCar) AS OldCar_Fords,
		 COUNT(Fords + Petrol) AS Petrol_Fords,
		 COUNT(OldCar + Petrol) AS Petrol_OldCar,
		 COUNT(OldCar + Petrol + Fords) AS Petrol_OldCar_Fords
        FROM (
        	SELECT
        		CASE WHEN `make` = 'Ford' THEN 1 END AS Fords,
        		CASE WHEN `power` = 'Petrol' THEN 1 END AS Petrol,
        		CASE WHEN `year_first_reg` < 2018 THEN 1 END AS OldCar
        	FROM motor_fleet
        	)
        AS Multiple_Combination_Aggregates

This gives the following result...

... and thus we have all of the aggregates in one dataset, from one query, and each one meaningfully titled.

Here is the data used for this example. It is given in ordinary .csv format so you can just copy it straight from the screen into a .csv file or your own database.

Table Name : motor_fleet
Data:
id,make,power,year_first_reg
1,BMW,Petrol,2015
2,Chrysler,Petrol,2016
3,Ford,Petrol,2016
4,Mercedes,Petrol,2021
5,Ford,Petrol,2021
6,Chrysler,Petrol,2015
7,Chrysler,Petrol,2020
8,Ford,Petrol,2019
9,Mercedes,Petrol,2020
10,Chrysler,Petrol,2017
11,Chrysler,Petrol,2020
12,Chrysler,Petrol,2020
13,Chrysler,Petrol,2015
14,Toyota,Petrol,2020
15,Chrysler,Petrol,2017
16,Ford,Petrol,2018
17,Mercedes,Petrol,2015
18,Chrysler,Petrol,2020
19,Ford,Petrol,2021
20,Toyota,Petrol,2018
21,Chrysler,Petrol,2017
22,Ford,Petrol,2018
23,Chrysler,Petrol,2018
24,BMW,Petrol,2015
25,Ford,Petrol,2017
26,Chrysler,Petrol,2021
27,Chrysler,Petrol,2016
28,Mercedes,Petrol,2021
29,Chrysler,Petrol,2021
30,Toyota,Petrol,2017
31,Mercedes,Petrol,2018
32,Ford,Petrol,2018
33,Ford,Petrol,2016
34,Chrysler,Petrol,2017
35,Ford,Petrol,2019
36,Chrysler,Petrol,2015
37,Ford,Petrol,2016
38,Chrysler,Petrol,2018
39,Ford,Petrol,2021
40,Ford,Petrol,2021
41,Chrysler,Petrol,2015
42,Chrysler,Petrol,2021
43,Ford,Petrol,2018
44,Ford,Petrol,2019
45,BMW,Petrol,2020
46,Chrysler,Petrol,2016
47,Ford,Petrol,2015
48,Ford,Petrol,2015
49,Chrysler,Petrol,2015
50,Ford,Petrol,2019
51,Chrysler,Petrol,2020
52,BMW,Petrol,2015
53,Chrysler,Petrol,2015
54,Ford,Petrol,2016
55,BMW,Petrol,2017
56,Ford,Petrol,2015
57,Chrysler,Petrol,2020
58,Ford,Petrol,2017
59,Ford,Petrol,2015
60,Ford,Petrol,2016
61,Chrysler,Petrol,2018
62,Mercedes,Petrol,2021
63,Mercedes,Petrol,2019
64,Chrysler,Petrol,2016
65,Mercedes,Petrol,2021
66,Mercedes,Petrol,2019
67,Ford,Petrol,2017
68,Mercedes,Diesel,2017
69,Ford,Diesel,2020
70,BMW,Diesel,2018
71,Chrysler,Diesel,2016
72,Chrysler,Diesel,2018
73,Ford,Diesel,2021
74,Chrysler,Diesel,2017
75,Chrysler,Diesel,2015
76,Ford,Diesel,2015
77,Toyota,Electric,2021
78,Ford,Electric,2020
79,Chrysler,Electric,2015
80,Chrysler,Electric,2021
81,BMW,Electric,2017
82,Ford,Electric,2016
83,Mercedes,Electric,2021
84,Ford,Electric,2019
85,Mercedes,Electric,2018
86,Chrysler,Electric,2019
87,Ford,Electric,2016
88,Mercedes,Electric,2017
89,Mercedes,Electric,2021
90,Chrysler,Electric,2021
91,Chrysler,Electric,2019
92,Ford,Electric,2017
93,Ford,Electric,2016
94,BMW,Electric,2016
95,Ford,Electric,2018
96,Mercedes,Electric,2018
97,Ford,Electric,2020
98,BMW,Electric,2019
99,Chrysler,Electric,2020
100,Ford,Electric,2016