Group Wise Total - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Group Wise Total

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-11-30 : 07:02:25

Hi,
Need Group wise total from below results. Will mention my Query and its output with Needed output.

Query:
-------
SELECT Classification,Sector,SUM(Stage1),SUM(Stage2),SUM(stage3)
FROM M_Prospect
GROUP BY TYPE,Sector

Result:
-------
[Classification] [SectorName] [ScreeningStage] [ICStage] [DDStage]
Financial Inclusion MicroCredit 2 2 2
Financial Inclusion Financial institution 2 2 2
Broader Inclusion Healthcare 2 2 2
Broader Inclusion Education 2 2 2
Broader Inclusion Vocational Training 2 2 2

Needed Result:
--------------
[Classification] [SectorName] [ScreeningStage] [ICStage] [DDStage]
Financial Inclusion MicroCredit 2 2 2
Financial Inclusion Financial institution 2 2 2
TOTAL FI TOTAL FI 4 4 4
Broader Inclusion Healthcare 2 2 2
Broader Inclusion Education 2 2 2
Broader Inclusion Vocational Training 2 2 2
TOTAL BI TOTAL BI 6 6 6

The above result is Group wise Results for Classifcation with its total. Please help me in fixing this.

Regards,
Kalaiselvan R
Love Yourself First....

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-30 : 08:18:30

This can be done in parts

1)First insert you result of following query in #temp table

I have hardcoded data and inserted into temp table as show below:

CREATE TABLE #TEMP([Classification] VARCHAR(MAX), [SectorName] VARCHAR(MAX), [ScreeningStage] INT, [ICStage] INT, [DDStage] INT)
INSERT INTO #TEMP
(Classification, SectorName, ScreeningStage, ICStage, DDStage)
SELECT 'Financial Inclusion', 'MicroCredit', 2, 2, 2 UNION ALL
SELECT 'Financial Inclusion', 'Financial institution', 2, 2, 2 UNION ALL
SELECT 'Broader Inclusion', 'Healthcare', 2, 2, 2 UNION ALL
SELECT 'Broader Inclusion', 'Education', 2, 2, 2 UNION ALL
SELECT 'Broader Inclusion', 'Vocational Training', 2, 2, 2

2)Run below query

INSERT INTO #TEMP
(Classification,ScreeningStage, ICStage, DDStage)
SELECT Classification,SUM(ScreeningStage),SUM(ICStage),SUM(DDStage)
FROM #TEMP
GROUP BY Classification

3)See result using below query
SELECT * FROM #TEMP ORDER BY Classification,SectorName

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-30 : 08:24:39

Alternatively, you may be able to use CUBE or ROLLUP. http://msdn.microsoft.com/en-us/library/ms189305.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources