Unraveling Subqueries
I have a rather complex query that returns one row, and I would like to expand it to return multiple rows, one for each region. Right now, I can use the following query to get statistics for one region at a time (in the example, in the West region):
SELECT Count(id) totalcustomers,
Sum(ordertotal) totaldollars
FROM (SELECT c.*
FROM customers c
JOIN customeraddresses ca
ON c.id = ca.customerid
WHERE ca.state IN (SELECT state
FROM stateregions
WHERE region = "West")
GROUP BY c.id) t
I would like to get a pivot table for all regions at once: the first column should have the names of the regions, and then the other columns should have the summary statistics as they already do.
A working example is available at http://sqlfiddle.com/#!2/297e86/5
In case something goes wrong with the SQLFiddle, here is the data structure:
CREATE TABLE customers
(
id INT,
ordertotal INT,
PRIMARY KEY (id)
);
INSERT INTO customers
(id, ordertotal)
VALUES
(1, 100),
(2, 250),
(3, 120);
CREATE TABLE customeraddresses (
customerid INT,
state char(2),
PRIMARY KEY (customerid,state)
);
INSERT INTO customeraddresses
(customerid, state)
VALUES
(1, 'ca'),
(2, 'ny'),
(3, 'ny'),
(3, 'ca');
CREATE TABLE stateregions (
region varchar(20),
state char(2),
PRIMARY KEY (region,state)
);
INSERT INTO stateregions
(region, state)
VALUES
('West', 'ca'),
('East', 'ny');
Obviously, the structure and meanings have been greatly simplified for this example, so please ask if something is not clear. Thank!
source to share
You just need JOIN
in the table stateregions
and then the GROUP BY
scope:
SELECT region, count(id) totalcustomers,sum(ordertotal) totaldollars
FROM customers c
JOIN customeraddresses ca ON c.id = ca.customerid
JOIN stateregions s on ca.state = s.state
GROUP BY s.region
This will return you one line per region:
REGION TOTALCUSTOMERS TOTALDOLLARS
East 2 370
West 2 220
Instead of potential duplicates, the c subquery distinct
should work for you:
SELECT region, COUNT(id), SUM(ordertotal)
FROM (
SELECT DISTINCT c.id, c.ordertotal, s.region
FROM customers c
JOIN customeraddresses ca ON c.id = ca.customerid
JOIN stateregions s on ca.state = s.state
) t
GROUP BY region
Result:
REGION TOTALCUSTOMERS TOTALDOLLARS
All 3 470
East 2 370
West 2 220
source to share
You have a problem because customers have multiple addresses. Consequently, they can be in several regions. The following approach picks for an arbitrary area for each client:
SELECT car.region, count(id) as totalcustomers,
sum(ordertotal) as totaldolalrs
FROM customers c JOIN
(select ca.customerid, max(region) as region
from customeraddresses ca join
stateregions sr
on sr.state = ca.state
group by ca.customerid
) car
ON c.id = car.customerid
GROUP BY car.region ;
A more sophisticated approach is to assign clients to all the regions they belong to and then either feed them or double them. The following options are listed below:
SELECT car.region, count(id) as totalcustomers,
sum(ordertotal) as totaldolalrs
FROM customers c JOIN
(select ca.customerid, max(region) as region
from customeraddresses ca join
stateregions sr
on sr.state = ca.state
) car
ON c.id = car.customerid
GROUP BY car.region ;
These queries are in this SQL Fiddle.
source to share