CODEWITHSUNDEEP
sqlsql-server
Richard West
Richard West

Reputation: 2226

SQL Query to count how many jobs requested a combination of services

I have a database in SQL Server that tracks job orders.

A job can have multiple services, and therefore multiple rows will exist in the table for each service.

Each record in the Job table has a JobID that is the same for all services requested for the given job.

I'm trying to create a query that will return a count showing how many jobs exist that have ordered a given combination of services.

So that if I have service A, B, and C, I am looking for a count of how many jobs included services A and C. They might have included other services, but at a minimum the job included both A and C.

Upvotes: 0

Views: 778

Answers (1)

shawnt00
shawnt00

Reputation: 17925

You don't even need to refer to the jobs table:

select count(*) as jobsWithAandC from (
    select s.jobID
    from services as s
    where s.service in ('A', 'C') /* you didn't tell us this column */
    group by s.jobID
    having count(s.service) = 2 /* distinct probably isn't necessary */
    /* here are other equivalent options */
    --1) having min(s.service) = 'A' and max(s.service) = 'C'
    --2) having count(case when s.service = 'A' then 1 else null end) > 0
    --      and count(case when s.service = 'C' then 1 else null end) > 0
) as T

Upvotes: 1

Related Questions