total columnwise sum - 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

total columnwise sum

Author

Topic

mahabirs
Starting Member

3 Posts

Posted - 2010-03-17 : 03:59:34

Hi All
I have a Student_mast table in which records are like that
studentid studentname districtid(FK) blockid(FK) classid(FK)
1 MS 1 2 1

2 KS 2 1 2
.... .. .. .. ...

7 ds 2 1 3

I want to out put Blockwise sum of these classes which is blow mentioned

Classes Name VI VII VIII

Block Name Bhatwari 15 20 15

Dunda 10 7 10

Total 25 27 25

Mahabir khatri

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-17 : 04:21:50

see with cube and rollup clause

for sample go through this link
http://chiragrdarji.wordpress.com/2008/09/09/group-by-cube-rollup-and-sql-server-2005/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 04:23:40

is this for reporting services report?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mahabirs
Starting Member

3 Posts

Posted - 2010-03-17 : 06:57:12

bye the way i have used roll up analytical function but in this only reteruning single column
whenever i want to retrieve multiple column it give the bugs. i need multiple column of the single . see the output like that
ClassName VI VII VIII
blockname A 10 12 10
B 20 5 7
Total 30 17 17

but the below query returns single column

SELECT CASE WHEN GROUPING(blockname) = 1 THEN 'Total'
ELSE blockname END blockname, count(sm.classid)VI
FROM Student_mast sm,BLOCK_MAST bm where bm.blockid=sm.blockid and sm.classid='1'
GROUP BY blockname with rollup

Mahabir khatri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 07:40:37

did you see my question? are you doing this for reports?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources