How To Sum Three or More int Columns

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I have a Table. The SID is declared nvarchar and the rest of the columns are declared with int datatypes. The int columns are populated with varying values of 1, 2 & 3 at random. My challenge is how to create a view or subprocedure that will add up the 1s, the 2s and the 3s Row by Row to produce separate result columns.

Posted 18-May-12 2:37am

Wosu

Updated 18-May-12 2:42am

v2

Add a Solution

Comments

Om Prakash Pant 18-May-12 8:39am

How do you want to display the result?

Wosu 18-May-12 9:06am

The result should be displayed as a View. Thanks

Maciej Los 18-May-12 8:49am

We need an example.

Wosu 18-May-12 9:41am

Imagine that you need to keep record of how many times in the days of a week or month or year that Rain fell, Sun shine and Dew covered a certain geographic region. Now imagine that this parameters are to be recorded in the mornings, Afternoons, and Evenings. So if I decide to assign 1 for Rainfall, 2 for Sun shine and 3 for Dew Cover as nature presents them in the Morning, Afternoon and Evening of the days of a certain week for instance. I know that I will need to divide the sum total of the Sun shine and Dew cover by 2 and 3 respectively to be able to have the accurate number of times the event occured. OK, my challenge is how to create a View that would accurately Sum the times Rainfell(i.e the 1s) and the times Sun shine(i.e the 2s) and the times Dew covered(i.e the 3s) Row wish. Thanks

Mohamed Mitwalli 18-May-12 9:25am

Share your code , what you did so far

Wendelius 18-May-12 17:00pm

If this isn't resolved yet, could you update the question with an example of the source data and the desired output

Wosu 21-May-12 8:58am

First I created a table t1 AS:
CREATE Table t1 (SID Varchar 15 NOT NULL, MM1 int NOT NULL,
MA1 int NOT NULL,
ME1 int NOT NULL,
TM1 int NOT NULL,
TA1 int NOT NULL,
TE1 int NOT NULL,
WM1 int NOT NULL,
WA1 int NOT NULL,
WE1 int NOT NULL,
ThM1 int NOT NULL,
ThA1 int NOT NULL,
The1 int NOT NULL,
FM1 int NOT NULL,
Fa1 int NOT NULL,
FE1 int NOT NULL)

NOTE
WHERE MM1 = Monday Morning Week1
MA1 = Monday Afternoon Week1
ME1 = Monday Evening Week1
-
-
-
-
-
FM1 = Friday Morning Week1
FA1 = Friday Afternoon Week1
FE1 = Friday Evening Week1.
If for instance the following values are inserted into t1:

Insert INTO t1(SID, MM1, MA1, ME1, TM1, TA1, TE1, WM1, WA1, WE1, ThM1, ThA1, ThE1, FM1, FA1, FE1)
VALUES(Wk001, 1, 1, 2, 3, 2, 2, 1, 2, 1, 3, 3, 1, 2, 1, 2)

My challenge is how to query the table t1 to Add up the 1s separately row-wise;
Also a query to Add up the 2s separately and divide the Sum of the 2s by 2 in order to obtain the actual times the value 2 occured. The same for the 3s.

In my effort I did this:

SELECT SID, MM1+MA1+ME1+TM1+TA1+TE1+WM1+WA1+WE1+ThM1+ThA1+ThE1+FM1+FA1+FE1 AS [Times Rain]
FROM dbo.t1
WHERE MM1=1 OR
MA1 = 1 OR
ME1 = 1 OR
TM1=1 OR
TA1=1 OR
TE1=1 OR
WM1=1 OR
WA1=1 OR
WE1=1 OR
ThM1=1 OR
ThA1=1 OR
ThE1=1 OR
FM1=1 OR
FA1=1 OR
FE1=1

Instead of this query giving me 6, the result i got was 27. Which is wrong!

I also tried to get the times the 2s occured as:

SELECT SID, MM1/2+MA1/2+ME1/2+TM1/2+TA1/2+TE1/2+WM1/2+WA1/2+WE1/2+ThM1/2+ThA1/2+ThE1/2+FM1/2+FA1/2+FE1/2 AS [Times Shine]
FROM dbo.t1
WHERE MM1=2 OR
MA1 = 2 OR
ME1 = 2 OR
TM1=2 OR
TA1=2 OR
TE1=2 OR
WM1=2 OR
WA1=2 OR
WE1=2 OR
ThM1=2 OR
ThA1=2 OR
ThE1=2 OR
FM1=2 OR
FA1=2 OR
FE1=2

This also failed to work for me!
Please a working solution will be appreciated, thanks.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-05-18T07:39:00

Still is not clear, but i've made an example.

I use PIVOT[^] statement to count.

Copy below code into MS SQK Managment Studio and paste into new query, than run.

SQL

IF NOT OBJECT_ID(N'#Weather') IS NULL 
	DROP TABLE #Weather

CREATE TABLE #Weather ([SID] VARCHAR(30), [TimeOfDay] VARCHAR(30), [Weather] VARCHAR(30))

INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Morning','Rainfall')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Afternoon','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Evening','Dew Cover')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Morning','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Afternoon','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Evening','Rainfall')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Europe','Morning','Dew Cover')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Morning','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Afternoon','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Evening','Rainfall')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Morning','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Afternoon','Sun')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Evening','Rainfall')
INSERT INTO #Weather ([SID] , [TimeOfDay], [Weather] )
	VALUES('Asia','Morning','Dew Cover')

DECLARE @cols VARCHAR(200)
DECLARE @dt VARCHAR(400)
DECLARE @pt VARCHAR(1000)

SET @cols = '[Dew Cover],[Rainfall],[Sun]'
SET @dt = 'SELECT * ' +
		'FROM #Weather '
SET @pt = 'SELECT [SID], [TimeOfDay], ' + @cols + ' ' + 
		'FROM (' + @dt + ') AS DT ' +
		'PIVOT (COUNT([Weather]) FOR [Weather] IN(' + @cols + ')) AS PT ' + 
		'ORDER BY PT.[SID]'
EXEC( @pt)

DROP TABLE #Weather

If you have any question, call ;)