Query is returning to rows instead of just one-CodePudding

I have a a query to return the dimensions of a package in M2 (square metres) and UN (unity's). With the current query it is returning two different lines, because I am using a CASE WHEN. This is the query:

SELECT DISTINCT(C.Package) 'Package',
    CASE S.Unity WHEN 'M2' THEN SUM(L.Qt*S.ConvEst) ELSE NULL END 'M2',
    CASE S.Unity WHEN 'UN' THEN SUM(L.Qt) ELSE NULL END 'UN'
FROM 
    PackageTable AS C
INNER JOIN 
    PackageTableRows L ON L.Package = C.Package
INNER JOIN 
    Products S ON S.Product = L.Product
WHERE 
    C.Package = '587496'
GROUP BY 
    C.Package, S.Unity

This result:

But what I really want is the query to return is something like this:

With only one line. I know for that I am not using CASE WHEN correctly and that is why I need your help.

CodePudding user response：

You have several problems here. Firstly, DISTINCT is not a function it's an operator. DISTINCT affects the entire dataset and causes only distinct rows to be returned. It's not DISTINCT ({Column Name}) it's SELECT DISTINCT {Columns}.

Next, you have both DISTINCT and GROUP BY; this is a flaw. A GROUP BY clause already causes your data to be returned in distinct groups, so a DISTINCT is both redundant and unneeded overhead. Get rid of the DISTINCT. If you are getting different results when you have a DISTINCT with a GROUP BY this is a strong indication that your GROUP BY clause is wrong and needs addressing (most likely you have too many columns in the clause).

Finally, when performing conditional aggregation the aggregate function should be around the entire CASE expression, not an expression in the THEN. Then also means that you then need to remove the column in your WHEN clause from the GROUP BY as I suspect the only reason you have it there is because you had to:

This results in:

SELECT C.Package AS Package,
       SUM(CASE S.Unity WHEN 'M2' THEN L.Qt * S.ConvEst END) AS M2,
       SUM(CASE S.Unity WHEN 'UN' THEN L.Qt END) AS UN
FROM dbo.PackageTable C
     INNER JOIN dbo.PackageTableRows L ON L.Package = C.Package
     INNER JOIN dbo.Products S ON S.Product = L.Product
WHERE C.Package = '587496'
GROUP BY C.Package;

CodePudding user response：

It's mostly correct. You need to GROUP BY only on C.Package to bring it into a single line. For this it should return 0 for case else conditions and aggregation should be on the full case conditions rather than only on the measure.

So it will look like this.

SELECT C.Package 'Package',
SUM(CASE S.Unity WHEN 'M2' THEN (L.Qt*S.ConvEst) ELSE 0 END ) as 'M2', 
SUM(CASE S.Unity WHEN 'UN' THEN SL.Qt ELSE 0 END) AS 'UN'
FROM PackageTable AS C
INNER JOIN PackageTableRows L ON L.Package=C.Package
INNER JOIN Products S ON S.Product=L.Product
WHERE C.Package='587496'
GROUP BY C.Package