Sum of multiple columns from single row

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

General SQL Server Forums

New to SQL Server Programming

Sum of multiple columns from single row

Author

Topic

The Sweg
Starting Member

29 Posts

Posted - 2010-03-02 : 13:46:30

Here's my table structure:

widgetname weld1 weld2 weld3 weld4 weld5
widget1 FW FW FW - _
widget2 FW FW FW FW NULL

I need to count the number of welds per widget, so my output needs to be:

widgetname total welds
widget1 3
widget2 4

I don't want to count any welds that contain '_' or '-' or that are NULL. I can't seem to get anything to work.

Any help is greatly appreciated as I'm completely new to SQL!
thanks in advance!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-02 : 14:22:32

SELECT
WidgetName,
CASE WHEN weld1 is not null THEN 1 ELSE 0 END
+ CASE WHEN weld2 is not null THEN 1 ELSE 0 END
+ CASE WHEN weld3 is not null THEN 1 ELSE 0 END
+ CASE WHEN weld4 is not null THEN 1 ELSE 0 END
+ CASE WHEN weld5 is not null THEN 1 ELSE 0 END
FROM yourTable
GROUP BY widgetname

Jim

Everyday I learn something that somebody else already knew

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 14:45:41

[code]SELECT WidgetName,
CASE WHEN weld1 IS NOT NULL AND weld1 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld2 IS NOT NULL AND weld1 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld3 IS NOT NULL AND weld1 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld4 IS NOT NULL AND weld1 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld5 IS NOT NULL AND weld1 NOT IN( '_','-') THEN 1 ELSE 0 END
FROM yourTable
[/code]

The Sweg
Starting Member

29 Posts

Posted - 2010-03-02 : 15:10:36

Thank you very much for your help! I think I am a bit closer, but all of my output in the 'Total Welds' column is '5' even though I know that some of those should be '0' or less than 5.

It seems as though it's not performing the WHEN...THEN of the CASE function. Is there more to this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-02 : 15:14:28

Between ms65g and me we make one wit!

SELECT WidgetName,
CASE WHEN weld1 IS NOT NULL AND weld1 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld2 IS NOT NULL AND weld2 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld3 IS NOT NULL AND weld3 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld4 IS NOT NULL AND weld4 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld5 IS NOT NULL AND weld5 NOT IN( '_','-') THEN 1 ELSE 0 END
FROM yourTable

Jim

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 15:20:58

where is SUM() and GROUP BY?
Or am I wrong?

No, you're never too old to Yak'n'Roll if you're too young to die.

The Sweg
Starting Member

29 Posts

Posted - 2010-03-02 : 15:26:37

jimf,

No, you're OK...I did actually see that you had the welds numbered that way and I made the adjustment in my query, but it still didn't work.

The Sweg
Starting Member

29 Posts

Posted - 2010-03-02 : 15:28:26

I also added a SUM(....) around the CASE statement and got the exact same result.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 15:37:43

This gives exactly what you want.
If that doesn't work for you the maybe your real data differs from example?

declare @widgets table (widgetname varchar(255), weld1 varchar(2), weld2 varchar(2), weld3 varchar(2), weld4 varchar(2), weld5 varchar(2))
insert @widgets
select 'widget1', 'FW', 'FW', 'FW', '-', '_' union all
select 'widget2', 'FW', 'FW', 'FW', 'FW', NULL

select * from @widgets


SELECT WidgetName,
CASE WHEN weld1 IS NOT NULL AND weld1 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld2 IS NOT NULL AND weld2 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld3 IS NOT NULL AND weld3 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld4 IS NOT NULL AND weld4 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld5 IS NOT NULL AND weld5 NOT IN( '_','-') THEN 1 ELSE 0 END
as [total welds]
FROM @widgets

No, you're never too old to Yak'n'Roll if you're too young to die.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 15:42:29

quote:

Originally posted by webfred

This gives exactly what you want.
If that doesn't work for you the maybe your real data differs from example?

declare @widgets table (widgetname varchar(255), weld1 varchar(2), weld2 varchar(2), weld3 varchar(2), weld4 varchar(2), weld5 varchar(2))
insert @widgets
select 'widget1', 'FW', 'FW', 'FW', '-', '_' union all
select 'widget2', 'FW', 'FW', 'FW', 'FW', NULL

select * from @widgets


SELECT WidgetName,
CASE WHEN weld1 IS NOT NULL AND weld1 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld2 IS NOT NULL AND weld2 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld3 IS NOT NULL AND weld3 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld4 IS NOT NULL AND weld4 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld5 IS NOT NULL AND weld5 NOT IN( '_','-') THEN 1 ELSE 0 END
as [total welds]
FROM @widgets

No, you're never too old to Yak'n'Roll if you're too young to die.

Or more clearly:


SELECT WidgetName,
  CASE WHEN weld1 IS NOT NULL AND weld1 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld2 IS NOT NULL AND weld2 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld3 IS NOT NULL AND weld3 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld4 IS NOT NULL AND weld4 NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN weld5 IS NOT NULL AND weld5 NOT IN( '_','-') THEN 1 ELSE 0 END
  FROM (SELECT 'widget1', 'FW', 'FW', 'FW', '-', '_' UNION ALL
        SELECT 'widget2', 'FW', 'FW', 'FW', 'FW', NULL) AS D(widgetname, weld1, weld2, weld3, weld4, weld5)

/*
WidgetName 
---------- -----------
widget1    3
widget2    4

(2 row(s) affected)
*/

The Sweg
Starting Member

29 Posts

Posted - 2010-03-02 : 15:48:49

webfred,

My data is different as I was trying to make it easier to post, but in essence, it's the same. All my fields are varchar(45) including my 'widgetname' which is my primary key on the 'widgets' table. I do get results, so it's not that it errors on the syntax, but all my results in the 'Total Welds' column are '5', which is how many columns I am counting.

Any other thoughts?
I do appreciate the help.
would it help if I pasted my SELECT statement here?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 15:52:47

Yes please post your select.
And you are sure you have NULL values and not only empty strings?

No, you're never too old to Yak'n'Roll if you're too young to die.

The Sweg
Starting Member

29 Posts

Posted - 2010-03-02 : 15:54:08

ok, now I'm really confused. Here's my SELECT statement exactly as I have it with my table names and all:

SELECT B1B_SPOOL_NO,
CASE WHEN 'W004_01-WELD-WPS' IS NOT NULL AND 'W004_01-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN 'W019_02-WELD-WPS' IS NOT NULL AND 'W019_02-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN 'W034_03-WELD-WPS' IS NOT NULL AND 'W034_03-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN 'W049_04-WELD-WPS' IS NOT NULL AND 'W049_04-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN 'W064_05-WELD-WPS' IS NOT NULL AND 'W064_05-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
AS [Total Welds]
FROM dbo.[Spool Welds]
GROUP BY B1B_SPOOL_NO;

Are you saying this should work?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 15:58:15

Yes but I don't see any reason for GROUP BY

No, you're never too old to Yak'n'Roll if you're too young to die.

The Sweg
Starting Member

29 Posts

Posted - 2010-03-02 : 15:58:58

I'm pretty sure I don't have empty strings, however, I added a '' to the NOT IN statement as follows to help with that. Hope fully this works. ? ( '_','-','')

The Sweg
Starting Member

29 Posts

Posted - 2010-03-02 : 16:00:59

OK, I took out the GROUP BY and still have the same result. I was thinking I had to group it because I was adding columns and wanted the output to be associated with the correct row, but I guess that's unnecessary. I gotta run, I'll check back in tomorrow.

Thanks again for helping with this!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-02 : 16:02:12

Yes it works.
To see the field values do this:
SELECT B1B_SPOOL_NO,
CASE WHEN 'W004_01-WELD-WPS' IS NOT NULL AND 'W004_01-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN 'W019_02-WELD-WPS' IS NOT NULL AND 'W019_02-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN 'W034_03-WELD-WPS' IS NOT NULL AND 'W034_03-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN 'W049_04-WELD-WPS' IS NOT NULL AND 'W049_04-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
+ CASE WHEN 'W064_05-WELD-WPS' IS NOT NULL AND 'W064_05-WELD-WPS' NOT IN( '_','-') THEN 1 ELSE 0 END
AS [Total Welds],
'W004_01-WELD-WPS',
'W019_02-WELD-WPS',
'W034_03-WELD-WPS',
'W049_04-WELD-WPS',
'W064_05-WELD-WPS'
FROM dbo.[Spool Welds]

edit: I hope this will give you an idea why each column is counted.

No, you're never too old to Yak'n'Roll if you're too young to die.

The Sweg
Starting Member

29 Posts

Posted - 2010-03-03 : 08:22:02

webfred,

I ran the above SELECT statement that you posted and this is what was returned (I am only showing the first row of 630 returned):

B1B_SPOOL_NO__Total Welds___(No column name)___(No column name)___(No column name)___(No column name)__(No column name)
spool1_________5________W004_01-WELD-WPS__W019_02-WELD-WPS__W034_03-WELD-WPS__W049_04-WELD-WPS__W064_05-WELD-WPS

All rows show exactly the same thing, except for the first column which is the spool name, that is different for each row returned. I looked at my table and for the first spool (spool1) I have 'S41' in the W004...column, 'S41' in the W019...column and the remaining weld columns have an underscore, so I'm expecting my 'Total Welds' value on the first row to be '2', not '5', but all rows return '5' no matter what. I'm new to databases and SQL (which is probably painfully obvious) so I'm not sure why this is behaving this way. Thanks for the help so far. Do you have any other suggestions?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-03 : 08:45:54

First it was my bad:
the additional columns in the select should not have quotes around it.
That's the reason why we cannot see the VALUES of the columns.
So please correct that and run it again.

Second I will come back when I have tested the statement because the underscore is a kind of joker in sql so I am not sure if we have to mask it some way inside the statement.

I will be back!

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-03 : 08:48:19

Ououh!
Not only the ADDITIONAL columns!
The other column names too! NO QUOTES around it please!

I think I was blind. That can already be the solution!!

No, you're never too old to Yak'n'Roll if you're too young to die.

The Sweg
Starting Member

29 Posts

Posted - 2010-03-03 : 08:58:31

webfred,

OK, I did a couple things. first, I set up a new table that follows the exact naming conventions as I initially started out with (widgets, weld1, etc...) and it worked! So, now I'm wondering why my table that I actually want this to work on isn't doing what I expect.

I took out the quotes and I got all kinds of errors. I think you are correct on the '_' deal. How could I mask that?

The errors look like this:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'W004_01'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'WELD'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'WPS'.

and on and on....

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources