September 5, 2002 at 12:44 pm
I'm trying to build a report in 1 SQL query and I'm having trouble creating 1 query that will do all that I want.
There are 2 tables - Company and User.
Every user is assigned 1 company, and 1 company has multiple users.
For each company I want to display COUNTS of various fields in the user table.
Example:
Num of users per company
Num of users per company that smoke
Num of users per company that are male
How can I write 1 sql statement that returns 1 row for each company, including a bunch of columns that correspond to the counts from the users table?
Thanks in advance!
Matthew
Matthew Mamet
September 5, 2002 at 1:49 pm
Possibly something like this might work for you.
create table company (id int identity, name char(10))
create table users (id int identity, lastname char(10), company int, sex char(1), smoke char(1))
insert into company values('Microsoft')
insert into company values('Oracle')
insert into company values('Ibm')
insert into users (lastname, company, sex, smoke) values('Gates', 1,'M','N')
insert into users (lastname, company, sex, smoke) values('Larsen', 1,'M','N')
insert into users (lastname, company, sex, smoke) values('Smith', 1,'F','Y')
insert into users (lastname, company, sex, smoke) values('Hanes', 2,'F','N')
insert into users (lastname, company, sex, smoke) values('Smith', 2,'M','Y')
insert into users (lastname, company, sex, smoke) values('Jones', 2,'F','N')
insert into users (lastname, company, sex, smoke) values('Dunn', 2,'F','Y')
insert into users (lastname, company, sex, smoke) values('Larsen', 2,'F','Y')
insert into users (lastname, company, sex, smoke) values('Bishop', 3,'F','N')
insert into users (lastname, company, sex, smoke) values('Duell', 3,'F','Y')
select x.name as Company,
case when Employees is null then 0 else Employees end as Employee,
case when Smokers is null then 0 else Smokers end as Smoker,
case when Males is null then 0 else Males end as Males
from
(select c.name , count(lastname) as "Employees"
from company c join users u on c.id = u.company
group by c.name) x left join
(select c.name, count(lastname) as "Smokers"
from company c join users u on c.id = u.company
where smoke = 'Y'
group by c.name) y on x.name = y.name left join
(select c.name, count(lastname) as "Males"
from company c left join users u on c.id = u.company
where sex = 'M'
group by c.name) z on x.name = z.name
drop table company
drop table users
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 6, 2002 at 4:22 am
Using Gregory's example tables.
Or just a variation which is a bit smaller and more concise.
SELECT
c.[name] AS Company,
COUNT(u.lastname) AS Employees,
SUM (case WHEN u.smoke = 'Y' THEN 1 else 0 END) as Smokers,
SUM (case WHEN u.sex = 'M' THEN 1 else 0 END) as Males
FROM
company c
INNER JOIN
users u
ON
c.id = u.company
GROUP BY
c.[name]
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply