Post reply

Results is doulbe the real results

  • November 30, 2008 at 8:02 am

    #72261

    I am trying to get the sum of the salaries for all employees in the accounting dept and a sum of all salaries in the sales department. This data exist in one table. The salaries are under one field.

    The results that I am getting is

    ACCOUNTING SALES

    £120,000.00 £140,000.00

    The correct results should be:

    ACCOUNTING SALES

    £60,000.00 £70,000.00

    SELECT distinct Sum(A.EMPSALARY) AS [Sum Of Accounting], Sum(B.EMPSALARY) AS [Sum Of Sales]

    FROM EMP AS A, EMP AS B

    WHERE A.DEPTNAME="Accounting" AND B.DEPTNAME="Sales";

  • November 30, 2008 at 8:44 am

    #905250

    Not sure why they're exactly doubled because you've created an "accidental cross-join" and they should be a lot more than just doubled. Moot point, though. Let's fix your problem...

    You don't need a self join at all... what you need is a "cross-tab" or "pivot". For more info on cross-tabs and pivots, please see the following article where you'll also find out why I don't care for "pivot's"...

    [font="Arial Black"]Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/font][/url]

    To answer your immediate question, here's how to use a cross-tab to easily solve your posted problem...

    SELECT SUM(CASE WHEN DeptName = 'Accounting' THEN EmpSalary ELSE 0 END) AS [Sum Of Accounting],

    SUM(CASE WHEN DeptName = 'Sales' THEN EmpSalary ELSE 0 END) AS [Sum Of Sales]

    FROM dbo.Emp

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 30, 2008 at 8:48 am

    #905251

    Just in case you already read the above, I had forgotten the "END" for each CASE in the code. I've corrected the code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 30, 2008 at 1:14 pm

    #905303

    As for why it's doubled, my guess is that you've got 2 people in accounting and 2 people in sales.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • December 1, 2008 at 1:42 am

    #905406

    Can you use the Case statement in Access?

  • December 1, 2008 at 4:02 am

    #905452

    grovelli (12/1/2008)

    Can you use the Case statement in Access?

    You can use TRANSFORM function in ACCESS to generate Cross-tab reports

    Madhivanan

    Failing to plan is Planning to fail

  • December 1, 2008 at 5:08 am

    #905504

    You can do it thus in Access:

    SELECT Sum(IIf([Dept]="Sales",[Salary],0)) AS Sales, Sum(IIf([Dept]="Accounting",[Salary],0)) AS Accounting

    FROM emp;

  • December 1, 2008 at 5:08 am

    #905506

    grovelli (12/1/2008)

    Can you use the Case statement in Access?

    My most sincere apologies... I was just cranking through some posts and didn't notice this was for MS Access... since I don't use Access, I don't know how to advise you except to explore "Transform" as suggested above.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 1, 2008 at 5:13 am

    #905507

    Jeff Moden (12/1/2008)

    grovelli (12/1/2008)

    Can you use the Case statement in Access?

    My most sincere apologies... I was just cranking through some posts and didn't notice this was for MS Access... since I don't use Access, I don't know how to advise you except to explore "Transform" as suggested above.

    The IIF function I have shown in my post can be used to emulate the CASE statement in some circumstances - Your algorithm is valid, just the syntax that varies - it offers a True/False choice which in this case is sufficient

  • December 1, 2008 at 5:44 am

    #905532

    Thanks, Chris... we posted at just about the same time and I didn't see your post when I posted. Thanks for bailing me out.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • December 1, 2008 at 6:59 am

    #905587

    Chris Quinn (12/1/2008)

    Jeff Moden (12/1/2008)

    grovelli (12/1/2008)

    Can you use the Case statement in Access?

    My most sincere apologies... I was just cranking through some posts and didn't notice this was for MS Access... since I don't use Access, I don't know how to advise you except to explore "Transform" as suggested above.

    The IIF function I have shown in my post can be used to emulate the CASE statement in some circumstances - Your algorithm is valid, just the syntax that varies - it offers a True/False choice which in this case is sufficient

    It's worth pointing out that CASE in T-SQL is actually a function and not a statement at all. Which is all the more reason to convert it to IIF() on Access.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 11 posts - 1 through 10 (of 10 total)

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