Artful Common Queries

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

Last updated 22 May 2009