asked on

SQL SUM Question Group By

Good afternoon,

I have a query where I do the following:
SELECT CustomerID,
SUM(Cost) Cost,
SUM(Quantity) Quantity,
LongShort,
SUM(Alpha) Alpha
FROM Customer
GROUP BY CustomerID, LongShort

What I need to do is if CustomerID = 1 then I want to make the LongShort return 'L' if the SUM(Quantity) >= 0 ELSE 'S' otherwise return the LongShort as it is. How can I do this because I can't place the SUM in the Group by with a CASE statement? I am trying to avoid doing nested queries etc. if possible.

ASKER CERTIFIED SOLUTION

zephyr_hex (Megan)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NerdsOfTech

You will need a subquery (with an alias) since a HAVING clause won't do the trick.

 SELECT 
    t2.CustomerID
  , t2.Cost
  , t2.Quantity
  , "LongShort" = 
    CASE 
     WHEN t2.CustomerID = 1 THEN "L"
     WHEN t2.Quantity >= 0 THEN "S"
     ELSE t1.LongShort
    END
  , t2.Alpha
 FROM Customer t1
 INNER JOIN
 (
  SELECT 
     CustomerID,
   , SUM(Cost) Cost,
   , SUM(Quantity) Quantity,
   , SUM(Alpha) Alpha
  FROM Customer
  GROUP BY CustomerID
 ) t2
 ON t1.CustomerID = t2.CustomerID

Open in new window

Vikas Garg

Hello,

You can try the below query

SELECT CustomerID,
              SUM(Cost) Cost,
              SUM(Quantity) Quantity,
              LongShort,
              SUM(Alpha) Alpha,
			  CASE WHEN CustomerID = 1 THEN
			  CASE WHEN SUM(Quantity) >=0 THEN 'L' ELSE 'S' END
			  ELSE LongShort END AS LongShortNew
FROM Customer
GROUP BY CustomerID, LongShort

Open in new window