Query Structure - Multiple tables

Question

Query Structure - Multiple tables

SQL.Chick

SSC Veteran

Points: 267
More actions
February 20, 2009 at 3:53 pm

#206195

This is what I have so far:
SELECT DISTINCT a.LASTNAME, a.FRSTNAME, a.SOCSCNUM, a.BRTHDATE, a.GENDER,
a.LOCATNID, a.STRTDATE, a.EMPLOYMENTTYPE, a.LASTDAYWORKED_I, b.PAYRTAMT,
c.CHEKDATE, c.UNTSTOPY, c.UPRTRXAM, d.ZIPCODE, e.YTD_Wages
FROM UPR00100 as a
INNER JOIN
UPR00400 as b
ON a.EMPLOYID = b.EMPLOYID
INNER JOIN UPR30300 as c
ON a.EMPLOYID = c.EMPLOYID
INNER JOIN UPR00102 as d
ON a.EMPLOYID = d.EMPLOYID
order by c.CHEKDATE, a.LASTNAME
INNER JOIN UPR00900 as e
ON a.EMPLOYID = d.EMPLOYID
I need to add this :
select SUM (e.GROSWAGS_1, e.GROSWAGS_2, e.GROSWAGS_3, e.GROSWAGS_4, e.GROSWAGS_5, e.GROSWAGS_6, e.GROSWAGS_7, e.GROSWAGS_8, e.GROSWAGS_9,
e.GROSWAGS_10, e.GROSWAGS_11, e.GROSWAGS_12) as e.YTD_Wages
from upr00900 as e
But the error messages when I run the queries are :
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FULL'.
Msg 174, Level 15, State 1, Line 14
The SUM function requires 1 argument(s).
:hehe:

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the sum() function does not take a comma delimited list: change all the commas to plus signs to sum all the columns and all the rows:

select SUM (e.GROSWAGS_1 +

e.GROSWAGS_2 +

e.GROSWAGS_3 +

e.GROSWAGS_4 +

e.GROSWAGS_5 +

e.GROSWAGS_6 +

e.GROSWAGS_7 +

e.GROSWAGS_8 +

e.GROSWAGS_9 +

e.GROSWAGS_10 +

e.GROSWAGS_11 +

e.GROSWAGS_12)

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!

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 2

[font="Verdana"]Just a little word of warning here: when you add those values, if any one of them is null, adding them together will give you a null as a result.

You may need to put an isnull(..., 0) around each of the values before adding them together.

[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Are you trying to add the 1 - 12 columns for each row, or are you trying to aggregate multiple rows together?

The latter is what SUM is for. If you're just trying to sum the 12 columns on each row, then this is what you want

SELECT DISTINCT a.LASTNAME, a.FRSTNAME, a.SOCSCNUM, a.BRTHDATE, a.GENDER,

a.LOCATNID, a.STRTDATE, a.EMPLOYMENTTYPE, a.LASTDAYWORKED_I, b.PAYRTAMT,

c.CHEKDATE, c.UNTSTOPY, c.UPRTRXAM, d.ZIPCODE,

e.GROSWAGS_1 + e.GROSWAGS_2 + e.GROSWAGS_3 + e.GROSWAGS_4 + e.GROSWAGS_5 + e.GROSWAGS_6 +

e.GROSWAGS_7 + e.GROSWAGS_8 + e.GROSWAGS_9 + e.GROSWAGS_10 + e.GROSWAGS_11 + e.GROSWAGS_12 AS YTD_Wages

FROM UPR00100 as a

INNER JOIN

UPR00400 as b

ON a.EMPLOYID = b.EMPLOYID

INNER JOIN UPR30300 as c

ON a.EMPLOYID = c.EMPLOYID

INNER JOIN UPR00102 as d

ON a.EMPLOYID = d.EMPLOYID

order by c.CHEKDATE, a.LASTNAME

INNER JOIN UPR00900 as e

ON a.EMPLOYID = d.EMPLOYID

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 4

[font="Verdana"]Unpivot and then use sum() might be abother option. Probably more effort than it's worth though.[/font]

SQL.Chick SSC Veteran Points: 267 More actions · Answer 5

SQL.Chick

SSC Veteran

Points: 267

February 23, 2009 at 7:58 am

#948607

Thank you VERY much !! 😀