Sum across categories

from the Artful Common Queries page

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',

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)

INSERT INTO ridings VALUES ('Essex'),('Malton'),('Riverdale');

CREATE TABLE parties (
  party char(12) NOT NULL default '',
  PRIMARY KEY  (party)

INSERT INTO parties VALUES ('Conservative'),('Liberal'),('Socialist');

And here is the query that does it:

  SUM(amt_spent) AS Total,
  (SUM(amt_spent)-SUM(CASE WHEN'Conservative' THEN NULL ELSE amt_spent END)) AS Cons,
  (SUM(amt_spent)-SUM(CASE WHEN'Liberal' THEN NULL ELSE amt_spent END)) AS Lib,
  (SUM(amt_spent)-SUM(CASE WHEN'Socialist' THEN NULL ELSE amt_spent END)) AS Soc
  (SELECT * FROM candidates
  INNER JOIN parties ON
  INNER JOIN ridings ON candidates.riding=ridings.riding) AS data

 ------- ------- ------- ------ 
| Total | Cons  | Lib   | Soc  |
 ------- ------- ------- ------ 
| 53000 | 10000 | 36000 | 7000 |
 ------- ------- ------- ------ 

Return to the Artful Common Queries page