CODEWITHSUNDEEP
sql-servert-sql
Cristian Contreras
Cristian Contreras

Reputation: 113

What is the difference between my 2 SUM(CASE()) examples?

So I have 2 queries, 1 works like I would expect and the other one doesn't. Here's the one that works like I expect, it's a SUMIF using a CASE statement:

SELECT
  PartNo,
  SUM(ActualPcsGood) AS Pcs,
  SUM(CASE WHEN Status = 'Current' THEN ActualPcsGood END) AS [Current],
  SUM(CASE WHEN Status = 'Pending' THEN ActualPcsGood END) AS [Pending],
  SUM(CASE WHEN Status = 'Future' THEN ActualPcsGood END) AS [Future],
  SUM(CASE WHEN Status = 'Finished' THEN ActualPcsGood END) AS [Finished]
FROM OrderRouting
WHERE PartNo LIKE '20004%'
GROUP BY PartNo;

Output:

enter image description here

Now I have this other query that is confusing me, here's the code:

SELECT
  JobNo,
  UnitPrice,
  SUM(CASE WHEN JobNo LIKE '10426%' THEN UnitPrice END) AS [OrderTotal]
FROM OrderDet
WHERE UnitPrice > 0
  AND JobNo LIKE '10426%'
GROUP BY JobNo, UnitPrice;

Output:

enter image description here

My question is why is the 3rd column exactly the same as the second one? It's my intention that the third column is supposed to total the entire thing, meaning that the value for the 3rd column would be exactly the same for all rows. Why is it not? What is the major difference between my 2 examples?

Upvotes: 0

Views: 83

Answers (5)

LAS
LAS

Reputation: 819

This isn't tested but here are some ideas:


select dtl.JobNo, dtl.UnitPrice, tot.UnitPrice SumPrice, (dtl.UnitPrice/tot.UnitPrice)*100 pctTot
from
(SELECT
  JobNo,
  UnitPrice
FROM OrderDet
WHERE UnitPrice > 0
  AND JobNo LIKE '10426%') dtl
cross join
(SELECT sum(UnitPrice) unitPrice
 FROM OrderDet 
 WHERE UnitPrice > 0
  AND JobNo LIKE '10426%') tot

OR

SELECT
  JobNo,
  UnitPrice,
  (select sum(UnitPrice) from OrderDet where UnitPrice > 0 and JobNo like '10426%') totPrice
FROM OrderDet
WHERE UnitPrice > 0
  AND JobNo LIKE '10426%'
GROUP BY JobNo;

Upvotes: 1

HappieFeet
HappieFeet

Reputation: 48

But why are you adding a case when you are already filtering by '10426%' condition? your query will return only those records with '10426%' as Job No. Grouping by both job and Unitprice will give you single row for different unit prices of same job. Below query should be enough. If you are having different unit price for '10426%', we cannot get unit price in a single row.

SELECT
  JobNo,
  SUM(UnitPrice) AS [OrderTotal]
FROM OrderDet
WHERE UnitPrice > 0
  AND JobNo LIKE '10426%'
GROUP BY JobNo;

Upvotes: 0

AB_87
AB_87

Reputation: 1156

Your first query groups by only PartNo. So you SUM with Case statement work for each unique PartNo.

Your second query however groups by JobNo and UnitPrice. so your SUM runs for each group of JobNo and UnitPrice, which is only single row. Hence same result as UnitPrice. Assuming each jobid as unique unit price try query below. You don't need CASE inside SUM as WHERE clause will take care of it.

SELECT
  JobNo,
  MIN(UnitPrice) as UnitPrice,
  SUM(UnitPrice) AS [OrderTotal]
FROM OrderDet
WHERE UnitPrice > 0
  AND JobNo LIKE '10426%'
GROUP BY JobNo;

Upvotes: 0

LAS
LAS

Reputation: 819

Group by sums the values within the grouping columns. Your grouping columns are Job and UnitPrice. Because you have a unique JobNo, UnitPrice, it's hard to see what it's doing. Try adding a duplicate UnitPrice, JobNo row in your data source so you can see what's actually doing.

I'm not sure why you would want to show the sum total in this way though. I would use a rollup which would show the total at the bottom.

Upvotes: 0

Mac Patterson
Mac Patterson

Reputation: 1

At first look everything looks fine. I would say that your number of records per unit price is one per job for the this record set.

Add a COUNT(*) to see how many records are being summed up.

The other thought is that you have a quantity field on the record and your case statement should really be:

SUM(CASE WHEN JobNo LIKE '10426%' THEN UnitPrice * Quantity END) AS [OrderTotal]

Hope that helps.

Upvotes: 0

Related Questions