group (Summarize?) rows with a common value

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)

group (Summarize?) rows with a common value

Author

Topic

aleangelico
Starting Member

4 Posts

Posted - 2007-11-29 : 18:02:11

Hi everybody. I need some help with this:

I have this table:


| ID | Type      | Amount |Group| 
------------------------------
|   1|Cash       |   100  |     |
|   2|Rate       |   200  |   10|
|   3|Restaurante|    50  |   10|
|   4|Bar        |    75  |   10|
|   5|Transfer   |    45  |   10|
|   6|Coke       |     2  |     |
|   7|Wine       |    25  |     |
|   8|Rate       |   200  |   11|
|   9|Restaurante|    50  |   11|
|  10|Bar        |    75  |   11|
|  11|Transfer   |    45  |   11|
|  12|Bar        |   110  |     |
|  13|Tour       |   130  |     |

I want to make a select grouping the rows with the same value in the Group column but not the ones blank or nulls.


| ID | Type      | Amount |Group| 
------------------------------
|   1|Cash       |   100  |     | - show alone
|   2|Rate       |   200  |   10| |   3|Restaurante|    50  |   10|  | Group
|   4|Bar        |    75  |   10|  | This
|   5|Transfer   |    45  |   10| /
|   6|Coke       |     2  |     | - show alone
|   7|Wine       |    25  |     | - show alone
|   8|Rate       |   200  |   11| |   9|Restaurante|    50  |   11|  | Group 
|  10|Bar        |    75  |   11|  | This
|  11|Transfer   |    45  |   11| /
|  12|Bar        |   110  |     | - show alone
|  13|Tour       |   130  |     | - show alone

So this should be the result:


| ID | Type      | Amount |Group| 
------------------------------
|   1|Cash       |   100  |     |
|   2|Group 10   |   370  |   10| --(200 + 50 + 75 + 45)
|   6|Coke       |     2  |     |
|   7|Wine       |    25  |     |
|   8|Group 11   |   370  |   11| --(200 + 50 + 75 + 45)
|  12|Bar        |   110  |     |
|  13|Tour       |   130  |     |

I began trying something like this, but this doesn't work the way I want:



	SELECT Group, 
	CASE 
		WHEN (Group IS NULL OR Group =0)
			THEN cc.Amount
		ELSE
			 (SELECT SUM(cf.Amount) FROM dbo.MyTable cf 
				GROUP BY Group 
				HAVING (Group = cc.Group))	
		END As Total
	FROM MyTable cc

Any ideas?

Regards
Alex

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-29 : 20:28:45

Try this


SELECT [ID], [Type], [Amount], [Group]
FROM MyTable
WHERE [Group] IS NULL
UNION
SELECT min([ID]), 'Group' + cast([Group] AS varchar(10)), sum([Amount]), [Group]
FROM MyTable
WHERE [Group] IS NOT NULL
GROUP BY [Group]
ORDER BY [ID]

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-29 : 20:30:58

Be careful not to have columns named 'Group', that will really be a pain to code with. Try this.


CREATE TABLE #Temp
(
Id int identity(1,1)
,[Type] varchar(20)
,Amount int
,[Group] int
)



INSERT INTO #Temp ([Type], Amount, [Group])

SELECT 'Cash'       ,   100  ,     NULL UNION ALL
SELECT 'Rate'       ,   200  ,   10 UNION ALL
SELECT 'Restaurante',    50  ,   10 UNION ALL
SELECT 'Bar'        ,    75  ,   10 UNION ALL
SELECT 'Transfer'   ,    45  ,   10 UNION ALL
SELECT 'Coke'       ,     2  ,    NULL  UNION ALL
SELECT 'Wine'       ,    25  ,     NULL UNION ALL
SELECT 'Rate'       ,   200  ,   11 UNION ALL
SELECT 'Restaurante',    50  ,   11 UNION ALL
SELECT 'Bar'        ,    75  ,   11 UNION ALL
SELECT 'Transfer'   ,    45  ,   11 UNION ALL
SELECT 'Bar'        ,   110  ,     NULL UNION ALL
SELECT 'Tour'       ,   130  ,     NULL

SELECT [Type], SUM(Amount) as Amount, NULL AS [Group]
FROM #Temp
WHERE [Group] IS NULL
GROUP BY [Type]

UNION ALL

SELECT  'Group ' + CAST([Group] AS VARCHAR),SUM(Amount),[Group]
FROM #Temp
WHERE [Group] IS NOT NULL
GROUP BY [Group]

aleangelico
Starting Member

4 Posts

Posted - 2007-11-30 : 12:19:11

Hey guys, you rock!

I tried snSQL's solution and worked like a charm! but of course thanks to both of you
cr8nk, you are right about the columns named GROUP but I put this name only for this post, in fact I'm spanish speaker so I use spanish names for the columns (what is great because is hard to have confusions between the code and the names)

Alex

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources