Code to summary row

Question

Code to summary row

adonetok

SSCrazy Eights

Points: 8395
More actions
December 15, 2010 at 12:07 pm

#230739

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]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I think you can use UNPIVOT to get your table in a normalized form (using integer values for the former column names). Based on that you'd simply use a SUM() WHERE col_name<=user_value.

If you'd like to see a coded version please provide ready to use sample data as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

adonetok SSCrazy Eights Points: 8395 More actions · Answer 2

adonetok

SSCrazy Eights

Points: 8395

December 15, 2010 at 12:34 pm

#1263079

I attached test data as Excel file.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

adonetok (12/15/2010)
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]

Can we make this a little more clear please? You say you've listed 10 columns, but it looks like 10 rows.

How many rows in your table? How many columns?

Do you want a row-wise SUM as your spreadsheet suggests, or a column-wise summary?

Are there groupings in your table?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 4

What is the primary key of that table?

Why do you have a SUM column in there when the values is supected to change based on user input?

Also, it seems like we have a slightly different interpretation of "ready to use sample data"...

See the first link in my signature for details.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

adonetok SSCrazy Eights Points: 8395 More actions · Answer 5

Sorry, I made mistake.

I attached new XLS file in which there are 12 columns including [ORDERID] and [SUM].

What I need is to update [Sum] column. [ORDERID] is primary key.

For example, if user select 3, [Sum] column should be as below:([01]+[02]+[03])

ORDERID-SUM

1-----------1

2-----------2

3-----------2

4-----------2

5-----------1

I need code to update [SUM] column based on user's selection. I will use value in [SUM] and [ORDERID] for application program.

Lowell SSC Guru Points: 323518 More actions · Answer 6

For reference, this is readily consumable data: Anyone can copy and paste this sample data into SSMS and then start working.

i changed all your column names.

SELECT 1 As OrderID,1 As C01,0 As C02,0 As C03,1 As C04,1 As C05,0 As C06,1 As C07,0 As C08,1 As C09,0 As C10,0 AS TheSum UNION ALL

SELECT 2,0,1,1,1,0,0,1,1,1,1,0 UNION ALL

SELECT 3,0,1,1,0,1,1,1,0,1,0,0 UNION ALL

SELECT 4,1,1,0,1,0,1,1,0,0,1,0 UNION ALL

SELECT 5,0,0,1,1,0,1,0,1,0,0,0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

LutzM SSC Guru Points: 107049 More actions · Answer 7

Thank you Lowell for the sample data.

I'm constantly requesting the OP to provide the data to verify that

a) the OP is willing to be actively involved in the solution finding process by reading and follwing some of the links provided and

b) the OP understand how to post sample data for the next question(s) to get faster answers

Sometimes it works, sometimes it doesn't and sometimes there are people around reacting before the OP does so I won't know...

Anyway, here's the coded version as per your sample data:

;

WITH cte AS

(SELECT 1 AS OrderID,1 AS C01,0 AS C02,0 AS C03,1 AS C04,1 AS C05,0 AS C06,1 AS C07,0 AS C08,1 AS C09,0 AS C10,0 AS TheSum UNION ALL

SELECT 2, 0,1,1,1,0,0, 1,1,1,1,0 UNION ALL

SELECT 3, 0,1,1,0,1,1, 1,0,1,0,0 UNION ALL

SELECT 4, 1,1,0,1,0,1, 1,0,0,1,0 UNION ALL

SELECT 5, 0,0,1,1,0,1, 0,1,0,0,0

)

SELECT OrderID,SUM(Val) AS total

FROM

(SELECT *

FROM cte) p

UNPIVOT

(Val FOR Selection IN

(C01,C02,C03,C04,C05,C06,C07,C08,C09,C10)

)AS unpvt

WHERE selection <='C06'

GROUP BY OrderID

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]