Grouping question
-
I have this table named EXE with 2 fields: NAME and QTY. What I want to do is group by NAME all the positives (QTY>0) and negatives (QTY<0)
So lets say I have these rows:
TESS -2
TESS -1
TESS +1
JOHN -1
JOHN +3
I want the result query to be
NAME POS NEG
TESS 1 -3
JOHN 3 -1
I tried using case when and group by like this:
SELECT
NAME,
'POS' = CASE WHEN QTY> 0 THEN SUM(QTY),
'NEG' = CASE WHEN QTY< 0 THEN SUM(QTY),
FROM
EXE
GROUP BY NAME
But Its not working because I have to put the comparing QTY in an aggregate function and none of them will give good results.
Is there a way to get that result without using group by?
-
slash3584 (3/9/2012)
I have this table named EXE with 2 fields: NAME and QTY. What I want to do is group by NAME all the positives (QTY>0) and negatives (QTY<0)So lets say I have these rows:
TESS -2
TESS -1
TESS +1
JOHN -1
JOHN +3
I want the result query to be
NAME POS NEG
TESS 1 -3
JOHN 3 -1
I tried using case when and group by like this:
SELECT
NAME,
'POS' = CASE WHEN QTY> 0 THEN SUM(QTY),
'NEG' = CASE WHEN QTY< 0 THEN SUM(QTY),
FROM
EXE
GROUP BY NAME
But Its not working because I have to put the comparing QTY in an aggregate function and none of them will give good results.
Is there a way to get that result without using group by?
I don't know of any. I usually go ahead and use 'group by'. Note that 'HARRY' has a null in the negative column!
CREATE TABLE #TEST1 (
NAME VARCHAR(20),
VAL INT
)
INSERT INTO #TEST1 VALUES ('TESS', -2)
INSERT INTO #TEST1 VALUES ('TESS', -1)
INSERT INTO #TEST1 VALUES ('TESS', +1)
INSERT INTO #TEST1 VALUES ('TESS', -2)
INSERT INTO #TEST1 VALUES ('JOHN', -1)
INSERT INTO #TEST1 VALUES ('JOHN', +3)
INSERT INTO #TEST1 VALUES ('JOHN', +5)
INSERT INTO #TEST1 VALUES ('JOHN', -2)
INSERT INTO #TEST1 VALUES ('JOHN', -4)
INSERT INTO #TEST1 VALUES ('JOHN', +1)
INSERT INTO #TEST1 VALUES ('HARRY', +4)
SELECT NAMES.NAME, SUM_POS, SUM_NEG FROM
(
SELECT DISTINCT NAME FROM #TEST1
) NAMES
LEFT JOIN
(
SELECT NAME, SUM(VAL) SUM_POS FROM #TEST1 WHERE VAL >0
GROUP BY NAME
) POS
ON
NAMES.NAME = POS.NAME
LEFT JOIN
(
SELECT NAME, SUM(VAL) SUM_NEG FROM #TEST1 WHERE VAL <=0
GROUP BY NAME
) NEG
ON
NAMES.NAME = NEG.NAME
-
Try this
SELECT
NAME,
'POS' = SUM(CASE WHEN QTY> 0 THEN QTY ELSE 0 END),
'NEG' = SUM(CASE WHEN QTY< 0 THEN QTY ELSE 0 END)
FROM
EXE
GROUP BY NAME
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Both ways work, thanks a lot guys
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply