Difficulties with join - SQL Server Forums

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

Difficulties with join

Author

Topic

Kuytu
Starting Member

2 Posts

Posted - 2008-05-27 : 04:19:21

Hello,
I'm trying to create a report that displays different sized customers in each office.
The tables look something like this:

client(id,size,representative)
representative(id,office)
office(id,name)

I would like the report to look something like this:

_______| all | <10 | 10-100 |
office1|
-----------------------------
office2|
------------------------------

I can get list of the offices and amount of all the clients like this:

SELECT office.name, COUNT(client.id) AS all
FROM office INNER JOIN
representative ON representative.office = office.id INNER JOIN
client ON client.representative = representative.id
group by office.name

But I just can't get my head around how to get rest of the information I want. If anyone has any suggestions how to proceed from here, I would be grateful.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 04:31:49

[code]SELECT office.name,
COUNT(client.id) AS [all],
SUM(CASE WHEN client.size < 10 THEN 1 ELSE 0 END) AS [<10],
SUM(CASE WHEN client.size BETWEEN 10 AND 100 THEN 1 ELSE 0 END) AS [10-100]
FROM office
INNER JOIN representative ON representative.office = office.id
INNER JOIN client ON client.representative = representative.id
group by office.name[/code]

E 12°55'05.25"
N 56°04'39.16"

Kuytu
Starting Member

2 Posts

Posted - 2008-05-27 : 04:40:52

Thank you!
I knew there had to be a simple solution for that, but I would have never figured that out by myself. Thank you, very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources