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.
sbornstein2Asked:
Who is Participating?
 
zephyr_hex (Megan)DeveloperCommented:
You can wrap your query in another SELECT that returns the results you want:

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

Open in new window


You may need to aggregate again since you are grouping by LongShort:

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

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
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

0
 
Vikas GargBusiness Intelligence DeveloperCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.