September 28, 2012 at 10:15 am
I hope my problem is self-explanatory based on the query I'm using and the result it returns.
SELECT p.product,
CASE WHEN[j.holiday] = 0 THEN sum(j.qty_delivered) END AS 'Standard Delivered',
CASE WHEN [j.holiday] <> 0 THEN sum(j.qty_delivered) END AS 'Holiday Delivered',
sum(j.qty_delivered) AS 'Total Delivered',
FROM jobs j
RIGHT OUTER JOIN products p
ON j.product_id = p.product_id
WHERE month(job_date) = 9 and
year(job_date) = 2012
GROUP BY p.product, j.holiday
[font="Courier New"]ProductStandard Delivered Holiday Delivered Total Delivered
Widget 156NULL156
GadgetNULL291291
GadgetNULL66
Widget NULL1010
GizmoNULL11
GizmoNULL00
Gadget8104NULL8104
GadgetNULL5151
NewGadget283NULL283
GizmoNULL55
GadgetNULL1414
GadgetNULL11
GizmoNULL2727
GizmoNULL11
Widget NULL44
GadgetNULL4242
NewGadgetNULL1212
GizmoNULL11
GizmoNULL11[/font]
What I need is a single row for each product type with the sum in each column.
The reason why I'm not getting this result is because I'm grouping by holiday which can contain any value between 0-9 (0 = not a holiday, 1-9 = different holiday locations.) So I'm seeing a product line for every holiday value.
I'm pretty sure I need to resolve this problem using a sub-query but I'm at a loss how to go about it.
Please can anybody advise?
September 28, 2012 at 10:31 am
I'm getting an error trying to run the query.
Are you sure it's right?
declare @jobs as table (product_id int, job_date date, holiday int, qty_delivered int);
insert @jobs values (1, '01 Sep 2012', 1, 25);
insert @jobs values (2, '01 Sep 2012', 1, 25);
declare @products as table (product_id int, product varchar(25));
insert @products values (1, 'product1');
insert @products values (2, 'product2');
select * from @jobs;
select * from @products;
--I hope my problem is self-explanatory based on the query I'm using and the result is returns.
SELECT p.product,
CASE WHEN[j.holiday] = 0 THEN sum(j.qty_delivered) END AS 'Standard Delivered',
--CASE WHEN [j.holiday] <> 0 THEN sum(j.qty_delivered) END AS 'Holiday Delivered',
sum(j.qty_delivered) AS 'Total Delivered'
FROM @jobs j
RIGHT OUTER JOIN @products p
ON j.product_id = p.product_id
WHERE month(job_date) = 9 and
year(job_date) = 2012
GROUP BY p.product, j.holiday
September 28, 2012 at 10:32 am
It's [j.holiday].....
Right -
September 28, 2012 at 10:38 am
Is this right?
declare @jobs as table (product_id int, job_date date, holiday int, qty_delivered int);
insert @jobs values (1, '01 Sep 2012', 1, 25);
insert @jobs values (1, '01 Sep 2012', 1, 25);
insert @jobs values (1, '01 Sep 2012', 1, 25);
insert @jobs values (1, '01 Sep 2012', 0, 40);
insert @jobs values (1, '01 Sep 2012', 0, 16);
insert @jobs values (2, '01 Sep 2012', 1, 55);
insert @jobs values (2, '02 Sep 2012', 0, 29);
declare @products as table (product_id int, product varchar(25));
insert @products values (1, 'product1');
insert @products values (2, 'product2');
select * from @jobs;
select * from @products;
--You need to wrap the whole CASE stmt in SUM(), & get rid of j.holiday from the GROUP BY.
SELECT p.product,
SUM(CASE WHEN j.holiday = 0 THEN j.qty_delivered END) AS 'Standard Delivered',
SUM(CASE WHEN j.holiday <> 0 THEN j.qty_delivered END) AS 'Holiday Delivered',
sum(j.qty_delivered) AS 'Total Delivered'
FROM @jobs j
RIGHT OUTER JOIN @products p
ON j.product_id = p.product_id
WHERE month(job_date) = 9 and
year(job_date) = 2012
GROUP BY p.product
September 28, 2012 at 1:36 pm
And for general performance reasons, get rid of the functions on the date/datetime column:
SELECT p.product,
SUM(CASE WHEN j.holiday = 0 THEN j.qty_delivered END) AS 'Standard Delivered',
SUM(CASE WHEN j.holiday <> 0 THEN j.qty_delivered END) AS 'Holiday Delivered',
sum(j.qty_delivered) AS 'Total Delivered'
FROM @jobs j
RIGHT OUTER JOIN @products p
ON j.product_id = p.product_id
WHERE j.job_date >= '20120901' AND j.job_date < '20121001'
GROUP BY p.product
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 28, 2012 at 5:25 pm
Perfect. 😀
Thanks Laurie.
September 28, 2012 at 5:26 pm
ScottPletcher (9/28/2012)
And for general performance reasons, get rid of the functions on the date/datetime column:
SELECT p.product,
SUM(CASE WHEN j.holiday = 0 THEN j.qty_delivered END) AS 'Standard Delivered',
SUM(CASE WHEN j.holiday <> 0 THEN j.qty_delivered END) AS 'Holiday Delivered',
sum(j.qty_delivered) AS 'Total Delivered'
FROM @jobs j
RIGHT OUTER JOIN @products p
ON j.product_id = p.product_id
WHERE j.job_date >= '20120901' AND j.job_date < '20121001'
GROUP BY p.product
Ah, okay. Will do that. Thanks Scott.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply