CODEWITHSUNDEEP
sqlselectjoincount
Jason Shaw
Jason Shaw

Reputation: 29

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.

Upvotes: 0

Views: 70

Answers (2)

DirkNM
DirkNM

Reputation: 2664

Maybe this helps (I didn't test it):

SELECT s1.LocationName,
       s1.TotalContracts AS cntAll,
       s2.TotalContracts AS cntLess35,
       s3.TotalContracts AS cntGreater35
  FROM(SELECT LOCATION.LocationName,
              COUNT(EB_MINMAX_VIEW.UnitQuan) TotalContracts
         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
      ) s1
  LEFT
  JOIN(SELECT LOCATION.LocationName,
              COUNT(EB_MINMAX_VIEW.UnitQuan) TotalContracts
         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
      ) s2 
    ON s1.LocationName = s2.LocationName
  LEFT
  JOIN(SELECT LOCATION.LocationName,
              COUNT(EB_MINMAX_VIEW.UnitQuan) TotalContracts
         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
      ) s3
    ON s1.LocationName = s3.LocationName

Another alternative:

SELECT LOCATION.LocationName,
       SUM(all),
       SUM(less35),
       SUM(greater35)
  FROM(
    SELECT LOCATION.LocationName,
           1 AS all,
           CASE WHEN UnitQuan < 35 THEN 1 ELSE 0 END less35,
           CASE WHEN UnitQuan > 35 THEN 1 ELSE 0 END greater35
      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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270613

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

Upvotes: 0

Related Questions