Interesting questions always come via my students. For example,
“Why does the selective aggregation sample return null values as
totals from the SUM() function in MySQL?”
First, here’s the code to build the sample table for the problem:
DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
( transaction_id int unsigned primary key auto_increment
, transaction_date date
, transaction_amount double );
INSERT INTO transaction
( transaction_date, transaction_amount )
VALUES
('2021-01-10', 56)
,('2021-02-14',23.02)
,('2021-03-31',31.06)
,('2021-01-01',.25)
,('2020-01-02', 52)
,('2020-02-08',22.02)
,('2020-03-26',32.06)
,('2020-01-12',.75);;
Now, here’s the selective aggregation query:
SELECT EXTRACT(YEAR FROM transaction_date) AS "Year"
, SUM(
CASE
WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN transaction_amount
END) AS "Jan"
, SUM( …[Read more]
