Goup by,Count and Sum of Tables

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)

Goup by,Count and Sum of Tables

Author

Topic

anabelle
Starting Member

19 Posts

Posted - 2009-01-30 : 01:28:59

Need Help, how wud i code to display this sample table, tablename:fruits
G1 G2 CountofApples
Apple A 1
Apple B 2
Apple C 4
Total 7

Orange A 10
Orange B 10
Orange C 10
Total 30

Let me know how wud i do it?i really need a little help...if someone does can help me u can write it or reply me on my email...thank u..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-30 : 01:33:54

try like this
select g1,g2,sum(countofapples)over (partition by g1 order by g1) from fruits

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-01-30 : 02:17:20

quote:
Originally posted by bklr

try like this
select g1,g2,sum(countofapples)over (partition by g1 order by g1) from fruits

How do you think that your query is going to give the desired output?

Anabelle try this

declare @table as table(g1 varchar(10),g2 varchar(5),cnt int)
insert into @table
select 'Ap','A',1 union all
select 'Ap','B',2 union all
select 'Ap','C',4 union all
select 'Or','A',10 union all
select 'Or','B',10 union all
select 'Or','C',10

;with cte(g1,g2,cnt,row)
as
(
select g1,g2,cnt,dense_rank()over(order by g1) from @table
union all
select cast(null as varchar(10)),cast('Total' as varchar(10)),sum(cnt),dense_rank()over(order by g1) from @table
group by g1
)

select g1,g2,cnt from cte order by row,g1 desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 02:25:56

[code]
SELECT G1,G2,COUNT(*) AS Cnt
FROM Table
GROUP BY G1,G2
WITH ROLLUP
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-30 : 02:40:37

[code]declare @table as table(g1 varchar(10),g2 varchar(5),cnt int)
insert into @table
select 'Ap','A',1 union all
select 'Ap','B',2 union all
select 'Ap','C',4 union all
select 'Or','A',10 union all
select 'Or','B',10 union all
select 'Or','C',10

SELECT
case when grouping(G1)=0 then G1 else 'total' end as G1,
case when grouping(G2)=0 then G2 else 'total' end as G2,
sum(cnt) AS Cnt
FROM @table
GROUP BY G1,G2
WITH ROLLUP[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

anabelle
Starting Member

19 Posts

Posted - 2009-01-30 : 03:33:31

hello everyone i am so happy with all ur answers, this is my first time to joine the forums and this is great! i wud surely learn a lot from here,i will try those codes later...thanks a lot for a great help it gives me an idea, i am a bit beginner on on my job so i am trying still to cope up and understand everyhting...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 03:38:36

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources