CODEWITHSUNDEEP
sql
Urjen
Urjen

Reputation: 47

Sql Select statement with different where statements

I am trying to create a select count statement where the where statement is different for each row.
For example i got the following table called dbo.job:

User:    Job:
Mark     Garbageman
Dirk     Garbageman
Henk     Garbageman
Steven   Garbageman
Mark     Delivery
Dirk     Delivery
Henk     Delivery
Steven   Delivery
Stevens  Delivery

Now i want to know how many user i got in table "job" with the job of garbageman, for this i use:

Select COUNT(user) as count from job WHERE job.job = 'Garbageman'

I will get the following result:

Count:
4

Now i also want to know how many users got the job of delivery in the same select statement, i will get something like:

Select COUNT(user) as GarbageCount, COUNT(user) as Deliverycount from job WHERE job.job = 'Garbageman'

Now i will get the following result:

GarbageCount:   DelivryCount:
4               4

But i want:

Now i will get the following result:

GarbageCount:   DelivryCount:
4               5

I know why my statement fails, that's because i am only using 1 where statement. But the where statement of both GarbageCount and DeliveryCount are different. And i have simply no clue how to create a query like that.

Someone has any suggestions?

Upvotes: 1

Views: 1949

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use conditional aggregation. The ANSI standard method is:

Select sum(case when j.job = 'Garbageman' then 1 else 0 end) as GarbageCount,
       sum(case when j.job = 'Delivery' then 1 else 0 end) as Deliverycount
from job j;

You do not specify the database, but MySQL also offers the shortcut of:

Select sum(j.job = 'Garbageman') as GarbageCount,
       sum(j.job = 'Delivery') as Deliverycount
from job j;

Upvotes: 0

Tilak
Tilak

Reputation: 30698

You can use Case When with Count.

http://peoplesoft.wikidot.com/conditional-counting-in-sql
MSDN reference ->http://msdn.microsoft.com/en-us/library/ms175997.aspx

select
    count(case when Job = 'Garbageman' then 1 end) as GarbageCount,
    count(case when Job = 'Delivery' then 1 end) as DeliveryCount
from Job

Upvotes: 2

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can achieve this using conditional sum()

select
sum(Job = 'Garbageman') as GarbageCount,
sum(Job = 'Delivery') as DelivryCount
from job

Upvotes: 0

Related Questions