Shantanu's Blog

Nirav has asked a question about
MySQL Aggregate functions and table joins

drop table transactions;
drop table transaction_products;
create table transactions (id int, totalPrice int, transDate DATE);
create table transaction_products (transId int, catId int);
insert into transactions values (1, 100, '2006-01-01');
insert into transactions values (1, 300, '2007-01-24');
insert into transactions values (2, 100, '2007-02-16');
insert into transaction_products values (1, 10);
insert into transaction_products values (2, 10);

SELECT SUM(t.totalPrice) FROM transactions t, transaction_products tp
WHERE t.id = tp.transId

It returns 500 that is correct total of all prices.
But now lets add the duplicate value into the table transaction_products.

insert into transaction_products values (1, 10);

If I run the same query above, now I get 900. It shows that the total value of 1 is duplicated and added to the total value. i.e. 400 + 400 + 100 = 900. This problem will be there when transaction_products table is not normalized. The rule says that the table should contain unique and atomic values and should point to the primary key. In this case, I can create a composite unique key on transaction_products like this...

alter ignore table transaction_products add unique (transId, catId)

Now the table transaction products has only one set of 1, 10 becasue it has deleted the duplicate one that I have just added. Hence it will allow us to run the very basic query mentioned in the post like...

SELECT SUM(t.totalPrice), tp.catId
FROM transactions t, transaction_products tp
WHERE t.id = tp.transId AND t.transDate BETWEEN "2007-01-01" AND "2007-10-31"
GROUP BY tp.catId

If adding the unique key is not an option, then the following query should work.

SELECT SUM(P.totalPrice) AS total, P.catId
FROM (SELECT DISTINCT t.totalPrice, t.id, t.transDate, tp.catId
FROM transactions t
INNER JOIN transaction_products tp
ON t.id = tp.transId
WHERE t.transDate BETWEEN '2007-01-01' AND '2007-10-31') AS P
GROUP BY P.catId

Or this should also work.

SELECT SUM(T.totalPrice) AS total, P.catId
FROM transactions AS T
INNER JOIN (SELECT catId, transId
FROM transaction_products tp
GROUP BY tp.transId) AS P
ON P.transId = T.id
AND transDate BETWEEN '2007-01-01' AND '2007-10-31'
GROUP BY P.catId

Labels: mysql case study