Combining Total Rows into one.

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

SQL Server 2005 Forums

Transact-SQL (2005)

Combining Total Rows into one.

Author

Topic

thewingser
Starting Member

8 Posts

Posted - 2009-07-14 : 16:13:52

Hey guys i'm new to the forum but not new to the sql scene.. i'm a software engineer and i'm stumped by this query i was ask to make so i'm wondering if you guys would be willing to help.. this is my problem

current code:


SELECT distinct a.TfmrType, 
(SELECT COUNT(*) FROM sde.transformerunit b WHERE a.TfmrType = b.TfmrType AND Owner = 'S') 'field1',
(SELECT COUNT(*) FROM sde.transformerunit b WHERE a.TfmrType = b.TfmrType AND Owner = 'P') 'field2',
(SELECT COUNT(*) FROM sde.transformerunit b WHERE a.TfmrType = b.TfmrType AND Owner = 'H') 'field3',
(SELECT COUNT(*) FROM sde.transformerunit b WHERE a.TfmrType = b.TfmrType) Total 

FROM sde.transformerunit a ORDER BY a.TfmrType

Result:

the desired output:

I need to group all the TfmrTypes that start with a "p", so 'P%' and add the totals of each of them. into one field called padmount..

could anyone give me a hand with this?

Thanks.

----
Eric Robinson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-14 : 16:30:27

Something similar to this?

SELECT		TfmrType,
		Field1,
		Field2,
		Field3,
		SUM(CASE WHEN SUBSTRING(TfmrType, 1, 1) = 'P' THEN Field0 ELSE 0 END) OVER () AS Padmount
FROM		(
			SELECT		TfmrType,
					COUNT(*) AS Field0,
					SUM(CASE WHEN [Owner] = 'S' THEN 1 ELSE 0 END) AS Field1,
					SUM(CASE WHEN [Owner] = 'P' THEN 1 ELSE 0 END) AS Field2,
					SUM(CASE WHEN [Owner] = 'H' THEN 1 ELSE 0 END) AS Field3
			FROM		sde.TransformerUnit
			GROUP BY	TfmrType
		) AS d
ORDER BY	TfmrType

You didn't post your expected output so I had to wing it.

N 56°04'39.26"
E 12°55'05.63"

thewingser
Starting Member

8 Posts

Posted - 2009-07-15 : 08:45:10

Sorry about that i edited my first post to display the desired output.

you almost got it, however i need to keep the current total row there and just merge all rows that start with a 'p'

here was your output:

this is what i need:

thank you peso

---
Eric Robinson

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-15 : 08:54:50

[code]
try this
SELECT TfmrType, Field1 AS 'Field1', Field2 AS 'Field2', Field3 AS 'Field3'
FROM (
SELECT DISTINCT a.TfmrType,
COUNT(CASE WHEN Owner = 'S' THEN 1 ELSE 0 END ) 'field1',
COUNT(CASE WHEN Owner = 'P' THEN 1 ELSE 0 END ) 'field2',
COUNT(CASE WHEN Owner = 'H' THEN 1 ELSE 0 END ) 'field3',
COUNT(*) AS Total
FROM sde.transformerunit a
GROUP BY TfmrType )s
WHERE SUBSTRING(TfmrType ,1,1) <> 'p'

UNION

SELECT 'Padamount', SUM(Field1) AS 'Field1', SUM(Field2) AS 'Field2', SUM(Field3) AS 'Field3'
FROM (
SELECT DISTINCT a.TfmrType,
COUNT(CASE WHEN Owner = 'S' THEN 1 ELSE 0 END ) 'field1',
COUNT(CASE WHEN Owner = 'P' THEN 1 ELSE 0 END ) 'field2',
COUNT(CASE WHEN Owner = 'H' THEN 1 ELSE 0 END ) 'field3',
COUNT(*) AS Total
FROM sde.transformerunit a
GROUP BY TfmrType )s
WHERE SUBSTRING(s.TfmrType ,1,1) ='p'
[/code]

thewingser
Starting Member

8 Posts

Posted - 2009-07-15 : 08:59:35

Even closer, however that total row is gone again and all the fields have the same value.

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-07-15 : 09:03:42

Will this also work?

SELECT SUM(F1) AS Field1,SUM(F2) AS Field2,SUM(F3)AS Field2,
SUM(F1+F2+F3)AS PADAMOUNT FROM
(
SELECT CASE SUBSTRING(TfmrType,1,1) WHEN 'P' THEN 1 ELSE 0 END AS NUM,SUM(Field1) AS F1,SUM(Field2) AS F2,SUM(Field3)AS F3
FROM dbo.transformerunit
GROUP BY TfmrType
) A
WHERE NUM=1

thewingser
Starting Member

8 Posts

Posted - 2009-07-15 : 09:07:47

Unfortunately its not going to work as field1, field2, and field3 are calculated columns, they are the number of transformers of a certain type (distribution, mini_pad, etc)

Regards,

---
Eric Robinson

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-15 : 09:10:32

[code]SELECT TfmrType,
SUM(CASE WHEN [Owner] = 'S' THEN 1 ELSE 0 END) AS Field1,
SUM(CASE WHEN [Owner] = 'P' THEN 1 ELSE 0 END) AS Field2,
SUM(CASE WHEN [Owner] = 'H' THEN 1 ELSE 0 END) AS Field3,
COUNT(*) AS Total
FROM sde.TransformerUnit
GROUP BY TfmrType

UNION ALL

SELECT 'Padmount',
SUM(CASE WHEN [Owner] = 'S' THEN 1 ELSE 0 END) AS Field1,
SUM(CASE WHEN [Owner] = 'P' THEN 1 ELSE 0 END) AS Field2,
SUM(CASE WHEN [Owner] = 'H' THEN 1 ELSE 0 END) AS Field3,
COUNT(*) AS Total
FROM sde.TransformerUnit
WHERE SUBSTRING(TfmrType, 1, 1) = 'P'[/code]

N 56°04'39.26"
E 12°55'05.63"

thewingser
Starting Member

8 Posts

Posted - 2009-07-15 : 09:29:34

that one seems to have a bit a problems lol but it still didn't combine the columns

i know its a tough one, i have a lot of problems with this database.. just because of poor design.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-15 : 09:39:30

Sorry, drop the GROUP BY for the second SELECT statement (after the UNION ALL).

N 56°04'39.26"
E 12°55'05.63"

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-07-15 : 09:45:34

Following up on Peso's solution:

WITH CTE
AS
(
SELECT name,CASE SUBSTRING(TfmrType,1,1) WHEN 'P' THEN 1 ELSE 0 END AS NUM,
SUM(CASE WHEN [Owner] = 'S' THEN 1 ELSE 0 END) AS F1,
SUM(CASE WHEN [Owner] = 'P' THEN 1 ELSE 0 END) AS F2,
SUM(CASE WHEN [Owner] = 'H' THEN 1 ELSE 0 END) AS F3
FROM sde.TransformerUnit
GROUP BY TfmrType
)
SELECT 'PADAMOUNT',SUM(F1) AS F1,SUM(F2) AS F2,SUM(F3)AS F3,SUM(F1+F2+F3)AS TOTAL FROM CTE
WHERE NUM=1
UNION
SELECT TfmrType,F1,F2,F3,SUM(F1+F2+F3)OVER(PARTITION BY TfmrType) AS TOTAL FROM CTE
WHERE NUM=0

thewingser
Starting Member

8 Posts

Posted - 2009-07-15 : 09:57:12

You my friend are an unsung hero to the sql community.

thank you so very much it works.