SumIf Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SumIf Help

Author

Topic

Kyriakos
Starting Member

13 Posts

Posted - 2007-10-24 : 12:18:08

For the following

name a b c d
-------------
alpha 1 3 4 3
beta 2 1 8 2
gamma 1 6 7 1
alpha 3 1 3 1
beta 1 1 1 1
gamma 2 1 2 2
delta 5 5 6 3

I need to sum up all the rows to <alpha> for [a] column and to <beta> for [b] column "zero"ing those columns except for the receipients, summing up the remaining cols by a group by.
The result should be:

name a b c d
------------------
alpha 15 4 7 4
beta 3 18 9 3
gamma 0 0 9 3
delta 0 0 6 3

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 12:20:43

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

Kyriakos
Starting Member

13 Posts

Posted - 2007-10-24 : 12:24:04

SQLS 2005

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 12:32:38

[code]DECLARE @sample TABLE
(
[name] varchar(10),
a int,
b int,
c int,
d int
)
INSERT INTO @sample
SELECT 'alpha', 1, 3, 4, 3 UNION ALL
SELECT 'beta', 2, 1, 8, 2 UNION ALL
SELECT 'gamma', 1, 6, 7, 1 UNION ALL
SELECT 'alpha', 3, 1, 3, 1 UNION ALL
SELECT 'beta', 1, 1, 1, 1 UNION ALL
SELECT 'gamma', 2, 1, 2, 2 UNION ALL
SELECT 'delta', 5, 5, 6, 3

SELECT s.[name], a = ISNULL(a, 0), b = ISNULL(b, 0), c, d
FROM
(
SELECT [name], c = SUM(c), d = SUM(d)
FROM @sample s
GROUP BY [name]
) s
left JOIN
(
SELECT [name] = 'alpha', a = SUM(a)
FROM @sample

UNION ALL

SELECT [name] = 'beta', a = SUM(a)
FROM @sample
WHERE [name] = 'beta'

) a ON s.[name] = a.[name]
left JOIN
(
SELECT [name] = 'alpha', b = SUM(b)
FROM @sample
WHERE [name] = 'alpha'

UNION ALL

SELECT [name] = 'beta', b = SUM(b)
FROM @sample

) b ON s.[name] = b.[name]

/*
name a b c d
---------- ----------- ----------- ----------- -----------
alpha 15 4 7 4
beta 3 18 9 3
delta 0 0 6 3
gamma 0 0 9 3

(4 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 12:32:56

[code]DECLARE @Sample TABLE (Name VARCHAR(9), a INT, b INT, c INT, d INT)

INSERT @Sample
SELECT 'alpha', 1, 3, 4, 3 UNION ALL
SELECT 'beta', 2, 1, 8, 2 UNION ALL
SELECT 'gamma', 1, 6, 7, 1 UNION ALL
SELECT 'alpha', 3, 1, 3, 1 UNION ALL
SELECT 'beta', 1, 1, 1, 1 UNION ALL
SELECT 'gamma', 2, 1, 2, 2 UNION ALL
SELECT 'delta', 5, 5, 6, 3

SELECT Name
a,
b,
c,
d
FROM (
SELECT 'alpha' AS Name,
SUM(a) AS a,
SUM(CASE WHEN Name = 'alpha' THEN b ELSE 0 END) AS b,
SUM(CASE WHEN Name = 'alpha' THEN c ELSE 0 END) AS c,
SUM(CASE WHEN Name = 'alpha' THEN d ELSE 0 END) AS d
FROM @Sample

UNION ALL

SELECT 'beta',
SUM(CASE WHEN Name = 'beta' THEN a ELSE 0 END),
SUM(b),
SUM(CASE WHEN Name = 'beta' THEN c ELSE 0 END),
SUM(CASE WHEN Name = 'beta' THEN d ELSE 0 END)
FROM @Sample

UNION ALL

SELECT Name,
0 AS a,
0 AS b,
SUM(c) AS c,
SUM(d) AS d
FROM @Sample
WHERE Name IN ('gamma', 'delta')
GROUP BY Name
) AS d
ORDER BY CASE Name
WHEN 'alpha' THEN 1
WHEN 'beta' THEN 2
WHEN 'gamma' THEN 3
WHEN 'delta' THEN 4
END[/code]

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 12:33:51

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 12:42:35

[code]SELECT [Name],
SUM(a) AS a,
SUM(b) AS b,
SUM(c) AS c,
SUM(d) AS d
FROM (
SELECT [Name],
0 AS a,
b,
c,
d
FROM @Sample
WHERE [Name] = 'alpha'

UNION ALL

SELECT 'alpha',
a,
0,
0,
0
FROM @Sample

UNION ALL

SELECT [Name],
a,
0,
c,
d
FROM @Sample
WHERE [Name] = 'beta'

UNION ALL

SELECT 'beta',
0,
b,
0,
0
FROM @Sample

UNION ALL

SELECT [Name],
0,
0,
c,
d
FROM @Sample
WHERE [Name] IN ('gamma', 'delta')
) AS d
GROUP BY [Name]
ORDER BY CASE Name
WHEN 'alpha' THEN 1
WHEN 'beta' THEN 2
WHEN 'gamma' THEN 3
WHEN 'delta' THEN 4
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Kyriakos
Starting Member

13 Posts

Posted - 2007-10-24 : 12:57:00

Actually the table I'm reffering to is not that small. The data are like

5060.00 V1 ALPHA
648.75 V2 ALPHA
14174.35 V3 ALPHA
70513.62 V4 ALPHA
1096.00 V2 BETA
320.00 V1 BETA
15240.28 V3 BETA
4267.58 V4 BETA
172.00 V3 GAMMA
3168.48 V4 GAMMA
43820.84 V1 GAMMA
958.00 V2 GAMMA
8605.75 V1 DELTA
6403.75 V3 DELTA
13296.59 V4 DELTA
1290.00 V2 DELTA
6683.20 V3 EPSILON
8568.44 V4 EPSILON
240.00 V2 EPSILON
4774.07 V1 EPSILON

and I need to add to DELTA the sum of all values if column B is <V2> whereas if the column B is <V3> the values should be sumed up to GAMMA. The remaining grouping by of course should remain intact!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 13:01:49

[code]DECLARE @Sample TABLE (Name VARCHAR(9), a INT, b INT, c INT, d INT)

INSERT @Sample
SELECT 'alpha', 1, 3, 4, 3 UNION ALL
SELECT 'beta', 2, 1, 8, 2 UNION ALL
SELECT 'gamma', 1, 6, 7, 1 UNION ALL
SELECT 'alpha', 3, 1, 3, 1 UNION ALL
SELECT 'beta', 1, 1, 1, 1 UNION ALL
SELECT 'gamma', 2, 1, 2, 2 UNION ALL
SELECT 'delta', 5, 5, 6, 3

SELECT [Name],
CASE
WHEN [Name] = 'alpha' THEN SUM(a) OVER (PARTITION BY NULL)
WHEN [Name] IN ('gamma', 'delta') THEN 0
ELSE a
END AS a,
CASE
WHEN [Name] = 'beta' THEN SUM(b) OVER (PARTITION BY NULL)
WHEN [Name] IN ('gamma', 'delta') THEN 0
ELSE b
END AS b,
c,
d
FROM (
SELECT [Name],
SUM(a) AS a,
SUM(b) AS b,
SUM(c) AS c,
SUM(d) AS d
FROM @Sample
GROUP BY [Name]
) AS d
ORDER BY CASE Name
WHEN 'alpha' THEN 1
WHEN 'beta' THEN 2
WHEN 'gamma' THEN 3
WHEN 'delta' THEN 4
END[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 13:03:34

CAN YOU PLEASE STOP WASTING OUR TIMES?

Why is it that hard to post full problem statement the first time?
Now, please post proper and accurate sample data the way I and khtan did earlier.
Also provide your expected output for the new sample data!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 13:30:49

Something like this?

DECLARE	@Sample TABLE (theData MONEY, theColumn CHAR(2), theRow VARCHAR(9))

INSERT	@Sample
SELECT	 5060.00, 'V1', 'ALPHA' UNION ALL
SELECT	  648.75, 'V2', 'ALPHA' UNION ALL
SELECT	14174.35, 'V3', 'ALPHA' UNION ALL
SELECT	70513.62, 'V4', 'ALPHA' UNION ALL
SELECT	 1096.00, 'V2', 'BETA' UNION ALL
SELECT	  320.00, 'V1', 'BETA' UNION ALL
SELECT	15240.28, 'V3', 'BETA' UNION ALL
SELECT	 4267.58, 'V4', 'BETA' UNION ALL
SELECT	  172.00, 'V3', 'GAMMA' UNION ALL
SELECT	 3168.48, 'V4', 'GAMMA' UNION ALL
SELECT	43820.84, 'V1', 'GAMMA' UNION ALL
SELECT	  958.00, 'V2', 'GAMMA' UNION ALL
SELECT	 8605.75, 'V1', 'DELTA' UNION ALL
SELECT	 6403.75, 'V3', 'DELTA' UNION ALL
SELECT	13296.59, 'V4', 'DELTA' UNION ALL
SELECT	 1290.00, 'V2', 'DELTA' UNION ALL
SELECT	 6683.20, 'V3', 'EPSILON' UNION ALL
SELECT	 8568.44, 'V4', 'EPSILON' UNION ALL
SELECT	  240.00, 'V2', 'EPSILON' UNION ALL
SELECT	 4774.07, 'V1', 'EPSILON'

;WITH Yak (theRow, theOrder, V1, V2, V3, V4)
AS (
	SELECT	p.theRow,
		p.theOrder,
		p.[V1],
		p.[V2],
		p.[V3],
		p.[V4]
	FROM	(
			SELECT	theRow,
				CASE theRow
					WHEN 'alpha' THEN 0
					WHEN 'beta' THEN 1
					WHEN 'gamma' THEN 2
					WHEN 'delta' THEN 3
					WHEN 'epsilon' THEN 4
				END AS theOrder,
				theData,
				theColumn
			FROM	@Sample
		) AS y
	PIVOT	(
			SUM(y.theData) FOR y.theColumn IN ([V1], [V2], [V3], [V4])
		) AS p
)

SELECT		theRow,
		CASE theOrder
			WHEN 0 THEN SUM(V1) OVER (PARTITION BY NULL)
			ELSE 0
		END,
		CASE theOrder
			WHEN 0 THEN V2
			WHEN 1 THEN SUM(V2) OVER (PARTITION BY NULL)
			ELSE 0
		END,
		CASE theOrder
			WHEN 0 THEN V3
			WHEN 1 THEN V3
			WHEN 2 THEN SUM(V3) OVER (PARTITION BY NULL)
			ELSE 0
		END,
		CASE theOrder
			WHEN 0 THEN V4
			WHEN 1 THEN V4
			WHEN 2 THEN V4
			WHEN 3 THEN SUM(V4) OVER (PARTITION BY NULL)
			ELSE 0
		END
FROM		Yak

E 12°55'05.25"
N 56°04'39.16"

Kyriakos
Starting Member

13 Posts

Posted - 2007-10-25 : 02:24:16

Yes thanks guys. Sorry about the inconvenience.

Kyriakos

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-25 : 04:35:57

Peter, this is the result of your query ?

theRow                                                                                            
--------- --------------------- --------------------- --------------------- --------------------- 
ALPHA                     .0000                 .0000                 .0000                 .0000 
BETA                      .0000                 .0000                 .0000                 .0000 
DELTA                     .0000                 .0000                 .0000                 .0000 
EPSILON                   .0000                 .0000                 .0000                 .0000 
GAMMA                     .0000                 .0000                 .0000                 .0000

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-25 : 04:37:12

Kyriakos,

What's the expected result for your sample data here ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 05:12:50

No, this is the outout from my latest suggestion

theRow  V1        V2       V3           V4
------  --------  -------  --------  --------
ALPHA   62580.66   648.75  14174.35  70513.62
BETA        0.00  4232.75  15240.28   4267.58
GAMMA       0.00     0.00  42673.58   3168.48
DELTA       0.00     0.00      0.00  99814.71
EPSILON	    0.00     0.00      0.00      0.00

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-25 : 05:17:13

strange, i just copy and paste your script in my QA and run and that's what i got. I must be missing something . . .

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 05:31:55

SQL Server 2005?