Code to summary row - 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 2005 Forums

Transact-SQL (2005)

Code to summary row

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-12-15 : 14:10:48

Can someone help me to code for row summary based on user's selection?
For example, one table is as below. There are 100 columns. (I only list 10 columns)
Each cell sored int 1 or 0.
If user selects 3, [sum]=[01]+[02]+[03]
If user selects 6, [sum]=[01]+[02]+[03]+[04]+[05]+[06]

[01] [int]
[02] [int]
[03] [int]
[04] [int]
[05] [int]
[06] [int]
[07] [int]
[08] [int]
[09] [int]
[10] [int]
[Sum] [int]

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-15 : 16:44:19

How about something like this??

CREATE TABLE [dbo].[Test](
	[col1] [int] NULL,
	[col2] [int] NULL,
	[col3] [int] NULL,
	[col4] [int] NULL,
	[col5] [int] NULL,
	[col6] [int] NULL,
	[UserSelectTTL]  AS ((((([Col1]+[Col2])+[col3])+[col4])+[col5])+[col6])
)

insert Into Test Values (0,1,1,1,0,0)
insert Into Test Values (0,1,0,1,0,0)
insert Into Test Values (0,1,1,1,1,0)
insert Into Test Values (0,1,1,1,1,1)
insert Into Test Values (0,1,1,1,0,0)
insert Into Test Values (0,0,0,1,0,0)
insert Into Test Values (0,1,1,1,0,0)
insert Into Test Values (0,0,0,0,0,0)
insert Into Test Values (1,1,1,1,0,0)
insert Into Test Values (0,1,1,1,0,0)
insert Into Test Values (1,1,1,1,1,1)
insert Into Test Values (0,1,1,1,0,0)

Results:


col1        col2        col3        col4        col5        col6        UserSelectTTL
----------- ----------- ----------- ----------- ----------- ----------- -------------
0           1           1           1           0           0           3
0           1           0           1           0           0           2
0           1           1           1           1           0           4
0           1           1           1           1           1           5
0           1           1           1           0           0           3
0           0           0           1           0           0           1
0           1           1           1           0           0           3
0           0           0           0           0           0           0
1           1           1           1           0           0           4
0           1           1           1           0           0           3
1           1           1           1           1           1           6
0           1           1           1           0           0           3

Thank You,

John

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-12-15 : 17:20:16

Thank you for help.
User may select a various number such as 45,91...
I need code to do it.

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-15 : 18:02:08

Then we need some additional information.

How and where are the users selecting the criteria??
Why the sum()? (More for curiosity)
As you can see from my example data with just 6 column,
differing selections can have the same sum() value.
Was my examlpe data set close to the what your data sset is?
Can you provide a better example of what your needing?

Thank You,

John

Maldred
Starting Member

28 Posts

Posted - 2010-12-15 : 18:39:18

The fields that you sum change based on what the user selected then you either will have to use a bunch of case statements in your SQL or dynamic SQL. I recommend you us dynamic SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources