Tables Roll-up Query - 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)

Tables Roll-up Query

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-09-12 : 07:14:58

Hi,
Am using a Transaction table which consists for entire data. Need to split the datas into 3 tables with roll-up depends on Columns datatype.
If its Numeric then Summation and if its BIT then Percentage.

Ex:
T_Table

[SID] [SSID] [VID] [NUMCOL1] [NUMCOL2] [BITCOL1]
1 1 1 100 200 1
1 1 2 150 50 0
1 1 3 100 150 1
1 2 1 250 50 0
1 2 2 300 70 0
1 2 3 100 100 1
2 1 1 100 100 1
2 1 2 100 100 0

From the above table need to roll up table for SID and SSID. Now its currently for VID data.

Results:
VID -> Table --> T_Table (The same Above)

FOR SSID:
[SID] [SSID] [NUMCOL1] [NUMCOL2] [BITCOL1]
1 1 350 400 66%
1 2 650 220 33%
2 1 200 200 50%

FOR SID:
[SID] [NUMCOL1] [NUMCOL2] [BITCOL1]
1 1000 620 50%
2 200 200 50%

The above is my expected output. For each roll up need to calculate from base table (T_Table).
Depends on Column Datatype calculation changes, If its Numeric SUM(Numeric Columns).
If its BIT then (COUNT(1)/Total COUNT)*100 is my calculation.

Please help me to fix this with suitable query.

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:29:44

[code]
SELECT SID,SSID,
SUM(NUMCOl1) AS NUMCOl1,
SUM(NUMCOl2) AS NUMCOl2,
SUM(CAST(BITCOL1 AS int))*100/COUNT(1) AS BITCOL1
FROM Table
GROUP BY GROUPING SETS ((SID,SSID),(SSID))
[/code]

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

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-09-12 : 12:12:09

No visakh,

I think am not clear to u. COUNT(1) is about BITCOL1 columns Active data.
So the calculation is (No of Active Columns/No Of Columns) * 100 from BITCOL1.
And some Values will be NULL or -1 in BITCOL1. Hence we cant do SUM for the column.

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 12:18:29

quote:
Originally posted by Kalaiselvan

No visakh,

I think am not clear to u. COUNT(1) is about BITCOL1 columns Active data.
So the calculation is (No of Active Columns/No Of Columns) * 100 from BITCOL1.
And some Values will be NULL or -1 in BITCOL1. Hence we cant do SUM for the column.

Regards,
Kalai

so you want only counts of non null values in numerator?

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

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-09-13 : 05:28:26

I got the solution.
Have used SUM(CASE WHEN BITCOL1=1 THEN 1 ELSE 0) for Active Counts
SUM(CASE WHEN BITCOL1=1 THEN 1 WHEN BITCOL1=0 THEN 1 ELSE 0) for Not Null counts.

Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 10:27:15

SUM(CASE WHEN BITCOL1=1 THEN 1 WHEN BITCOL1=0 THEN 1 ELSE 0)
is same as

SUM(CASE WHEN BITCOL1 IS NOT NULL THEN 1 ELSE 0 END)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources