How can I return 3 COUNT columns from the same table when JOIN with other tables as well?

I would like to know how I can return several COUNTs for the following:

I have 27 work sites, each site has an employee with a contract, I have wrote a script to return one actual column of data (Work sites, 27 in total), I then added a COUNT to count how many contracts/staff I have in each site. I have had to use 3 tables to get the data.

what I would like to do now is add two more columns, one that shows how many contracts I have “Under 35 hours” and one that shows how many I have “Over 35 hours”

This is what I have to return site names and total contracted hours:

SELECT
      LOCATION.LocationName,    
      COUNT (EB_MINMAX_VIEW.UnitQuan) AS 'Total Contracts'   
FROM 
      LOCATION   
JOIN 
      eb_view on eb_view.locationcounter = location.locationcounter     
JOIN 
      EB_MINMAX_VIEW on EB_MINMAX_VIEW.ebcounter = eb_view.ebcounter    
GROUP BY 
      LOCATION.LocationName

Then if i want to return contacts under 35 hours then i have to write this:

SELECT
      LOCATION.LocationName,
      COUNT (EB_MINMAX_VIEW.UnitQuan) AS 'Total Contracts'    
FROM
      LOCATION    
JOIN
      eb_view on eb_view.locationcounter = location.locationcounter     
JOIN
      EB_MINMAX_VIEW on EB_MINMAX_VIEW.ebcounter = eb_view.ebcounter    
WHERE
      UnitQuan < 35    
GROUP BY
      LOCATION.LocationName

and this will give me the number of contracts less than 35 for all sites, but I want to include this in the final table i.e. site name, number of total contracts per site, number of contrast < 35 for all sites, and a column for number of contracts > 35 for each site.

Answer

This is a simpler form of DirkNM’s query:

SELECT l.LocationName,
       count(*) AS all,
       SUM(CASE WHEN ebmm.UnitQuan < 35 THEN 1 ELSE 0 END) as less35,
       SUM(CASE WHEN ebmm.UnitQuan > 35 THEN 1 ELSE 0 END) greater35
FROM LOCATION JOIN
     eb_view eb
     ON eb.locationcounter = l.locationcounter JOIN
     EB_MINMAX_VIEW ebmm
     on ebmm.ebcounter = eb.ebcounter
GROUP BY l.LocationName

Advertisement

Answer