Sum across categories
You often need to sum across several categories to total customer purchase amounts, salesperson sales amounts, political party election spending, etc.
For this example assume three tables: candidates, parties and ridings. You want to get the total amount spent in all ridings by every party in one output row. Here is the schema:
CREATE TABLE candidates (
id int(11) NOT NULL default '0',
`name` char(10) ,
riding char(12) ,
party char(12) ,
amt_spent decimal(10,0) NOT NULL default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO candidates
VALUES (1,'Anne Jones','Essex','Liberal','5000'),
(2,'Mary Smith','Malton','Liberal','7000'),
(3,'Sara Black','Riverdale','Liberal','15000'),
(4,'Paul Jones','Essex','Socialist','3000'),
(5,'Ed While','Essex','Conservative','10000'),
(6,'Jim kelly','Malton','Liberal','9000'),
(7,'Fred Price','Riverdale','Socialist','4000');
CREATE TABLE ridings (
riding char(10) NOT NULL default '',
PRIMARY KEY (riding)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO ridings VALUES ('Essex'),('Malton'),('Riverdale');
CREATE TABLE parties (
party char(12) NOT NULL default '',
PRIMARY KEY (party)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO parties VALUES ('Conservative'),('Liberal'),('Socialist');
And here is the query that does it:
SELECT
SUM(amt_spent) AS Total,
(SUM(amt_spent)-SUM(CASE WHEN data.party='Conservative' THEN NULL ELSE amt_spent END)) AS Cons,
(SUM(amt_spent)-SUM(CASE WHEN data.party='Liberal' THEN NULL ELSE amt_spent END)) AS Lib,
(SUM(amt_spent)-SUM(CASE WHEN data.party='Socialist' THEN NULL ELSE amt_spent END)) AS Soc
FROM
(SELECT * FROM candidates
INNER JOIN parties ON candidates.party=parties.party
INNER JOIN ridings ON candidates.riding=ridings.riding) AS data
------- ------- ------- ------
| Total | Cons | Lib | Soc |
------- ------- ------- ------
| 53000 | 10000 | 36000 | 7000 |
------- ------- ------- ------