SQL count of intersections with total field

For example we have this ServiceRequests source table in database:

| Id | ClientDepartment | ServiceType | DateTimeServiced |
|----|------------------|-------------|------------------|
| 1  | Sales            | Networking  | 15.01.17         |
| 2  | Development      | Networking  | 14.02.17         |
| 3  | Development      | Networking  | 09.04.17         |
| 4  | Sales            | Software    | 11.03.17         |
| 5  | Sales            | Hardware    | 30.03.17         |
| 6  | Development      | Hardware    | 15.04.17         |

Need to make SQL select query and get result:

|Client\Service| Networking | Software | Hardware | Total |
|--------------|------------|----------|----------|-------|
| Sales        | 1          | 1        | 1        | 3     |
| Development  | 2          | 0        | 1        | 3     |

Where numbers are count of intersections between ServiceType and ClientDepartment (services per department).

Trying something like this gives very wrong result:

select ClientDepartment,
    (select count(t1.ClientDepartment)
        from ServiceRequests t1
        where t1.ServiceType = 'Networking' 
    ) as Networking,
    (select count(t1.ClientDepartment)
        from ServiceRequests t1
        where t1.ServiceType = 'Software'
    ) as Software,
    (select count(t1.ClientDepartment)
        from ServiceRequests t1
        where t1.ServiceType = 'Hardware' 
    ) as Hardware,
    (select count(t1.ClientDepartment)
        from ServiceRequests t1
    ) as Total
from ServiceRequests
group by ClientDepartment

|Client\Service| Networking | Software | Hardware | Total |
|--------------|------------|----------|----------|-------|
| Sales        | 3          | 1        | 2        | 6     |
| Development  | 3          | 1        | 2        | 6     |

Hope for help, create example table code

1 answer

  • answered 2017-06-17 19:49 scaisEdge

    You could use sum on CASE WHEN and group by
    in this way you dn't need subselect for each count

      select 
            ClientDepartment
          , sum(case when ServiceType = 'Networking' then 1 else 0 end )as Networking
          , sum(case when ServiceType = 'Software' then 1 else 0 end ) as Software
          , sum(case when ServiceType = 'Hardware' then 1 else 0 end ) as Hardware
          , sum(case when ServiceType = 'Networking' then 1 else 0 end ) +
            sum(case when ServiceType = 'Software' then 1 else 0 end ) +
            sum(case when ServiceType = 'Hardware' then 1 else 0 end ) as  Total 
      from ServiceRequests
      group by ClientDepartment
    

    this should work for both the DB ..