Post reply

Query Structure - Multiple tables

  • 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:

  • February 20, 2009 at 4:42 pm

    #948040

    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!

  • February 22, 2009 at 2:07 pm

    #948343

    [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]

  • February 22, 2009 at 3:07 pm

    #948364

    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

  • February 22, 2009 at 3:11 pm

    #948365

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

  • February 23, 2009 at 7:58 am

    #948607

    Thank you VERY much !! 😀

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

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