Results is doulbe the real results

Question

Results is doulbe the real results

cindy_sinath

Old Hand

Points: 345
More actions
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";

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

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

Jeff Moden SSC Guru Points: 1004469 More actions · Answer 1

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)

Jeff Moden SSC Guru Points: 1004469 More actions · Answer 2

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

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]

grovelli-262555 SSCrazy Points: 2633 More actions · Answer 4

grovelli-262555

SSCrazy

Points: 2633

December 1, 2008 at 1:42 am

#905406

Can you use the Case statement in Access?

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

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

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 6

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;

Jeff Moden SSC Guru Points: 1004469 More actions · Answer 7

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)

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004469 More actions · Answer 9

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

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]