Using Group by Clause - 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

Using Group by Clause

Author

Topic

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-18 : 08:51:50

Hi, i have this table


------  -----
Cat	Value
------  ------
A_4D	100
A_E44	100
A_3DF	200
A_RT	300
B_RY75	120
B_RR	129
B_ERT	123
C_DGTY	134
C_F	234
C_F	444
D_FGDS	23
D_F	3444
D_DFG	435
D_S	345
D_SDF	345
D_G	345
D_DFG	3344
--------------

i need to generate a summary of values group by first letter in the first colum(i.e. group by A,B,C AND D)
Finally it should look like this


----------      -----
Cat	        Value
----------      -----
A_ Total	700
B_ Total	372
C_ Total	812
D_ Total	8281
Grand Total	10165
---------------------

please help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-18 : 08:59:46

select left(cat,charindex('_',cat)-1) as cat, sum(value) as value from table
group by left(cat,charindex('_',cat)-1)

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-18 : 09:02:26

SELECT LEFT(t.Cat,1)+'_ Total' as CAT
,SUM(t.Value) as [Value]
FROM
YourTable t
GROUP BY LEFT(t.Cat,1)

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-18 : 09:16:35

Great, Thanks a lot Madhivanan.

But i am not able to get other columns(other than cat).
actually i have 3 columns like


------	-----	-----	-----
Cat	Value 1	Value 2	Value 3
------	------	------	------
A_4D	100	44	3
A_E44	100	56	56
A_3DF	200	6	6
A_RT	300	6	345
B_RY75	120	5	6
B_RR	129	6	345
B_ERT	123	6	456
C_DGTY	134	4	56
C_F	234	5	5
C_F	444	4	56
D_FGDS	23	6	6
D_F	3444	5	5
D_DFG	435	5	6
D_S	345	5	5
D_SDF	345	5	6
D_G	345	5	5
D_DFG	3344	5	5
------	------	------	------

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-18 : 09:33:19

quote:
Originally posted by krishna_yess

Great, Thanks a lot Madhivanan.

But i am not able to get other columns(other than cat).
actually i have 3 columns like

------ ----- ----- -----
Cat Value 1 Value 2 Value 3
------ ------ ------ ------
A_4D 100 44 3
A_E44 100 56 56
A_3DF 200 6 6
A_RT 300 6 345
B_RY75 120 5 6
B_RR 129 6 345
B_ERT 123 6 456
C_DGTY 134 4 56
C_F 234 5 5
C_F 444 4 56
D_FGDS 23 6 6
D_F 3444 5 5
D_DFG 435 5 6
D_S 345 5 5
D_SDF 345 5 6
D_G 345 5 5
D_DFG 3344 5 5
------ ------ ------ ------

So, what is your expected result?

Madhivanan

Failing to plan is Planning to fail

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-18 : 10:48:20

i need result like this


Cat	Value1	Value2	Value3
A Total	700	112	410
B Total	372	17	807
C Total	812	13	117
D Total	8281	36	38
"Grand 
Total"	10165	178	1372

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-18 : 11:41:04

quote:
Originally posted by krishna_yess

i need result like this

Cat Value1 Value2 Value3
A Total 700 112 410
B Total 372 17 807
C Total 812 13 117
D Total 8281 36 38
"Grand
Total" 10165 178 1372

You can easily modify the query
Try it

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 11:41:13

[code]SELECT COALESCE(PARSENAME(REPLACE(Cat,'_','.'),2),PARSENAME(REPLACE(Cat,'_','.'),1)),
SUM(Value1) AS Value1,
SUm(Value2) AS Value2,
SUM(Value3) AS Value3
FROM
GROUP BY COALESCE(PARSENAME(REPLACE(Cat,'_','.'),2),PARSENAME(REPLACE(Cat,'_','.'),1))[/code]

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2008-09-18 : 11:54:20

i got it
thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 12:39:52

quote:
Originally posted by krishna_yess

i got it
thanks a lot

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources