Combination of SQL Select statements

Question

I am trying to take a "snapshot" of a state of some tables of our database. I have finished a few of them and I am currently working on the last, however for the last one I need to query two different tables.

So far have tried the following:

SELECT
     COUNT(Name) AS CRIT,
     branch
FROM
    (SELECT
         A.Name,
         A.branch,
         B.score,
         B.last_found
     FROM  
         TableA AS A
     INNER JOIN 
         TableB AS B ON A.Name = V.Name
     WHERE
         B.state <> 'DONE'
         AND DATEDIFF(day, B.last_found, GETDATE()) > 30
         AND CAST(B.score AS float) > 8) AS X
     GROUP BY
         branch

This will return me a portion of what I need:

(https://i.sstatic.net/fGIgo.png)

I can slightly modify the code:

SELECT
     COUNT(Name) AS LOW,
     branch
FROM
    (SELECT
         A.Name,
         A.branch,
         B.score,
         B.last_found
     FROM   
         TableA AS A
     INNER JOIN 
         TableB AS B ON A.Name = V.Name
     WHERE
         B.state <> 'DONE'
         AND DATEDIFF(day, B.last_found, GETDATE()) > 30
         AND CAST(B.score AS float) < 4) AS X
     GROUP BY
         branch

To get the following:

(https://i.sstatic.net/PILiX.png)

This is once again another portion of what I need. The final output should be a combination of the two above queries and should have 3 columns:

CRIT   LOW   Branch  

Apologies if I am unclear and thank you for helping!!

Answer 1

You want conditional aggregation (CASE expression inside an aggregation function):

SELECT
   a.branch,
   COUNT(CASE WHEN CAST(b.score as float) > 8 THEN 1 END) AS crit,
   COUNT(CASE WHEN CAST(b.score as float) < 4 THEN 1 END) AS low
FROM tablea AS a
INNER JOIN tableb AS b ON b.name = a.name
WHERE b.state <> 'DONE'
AND DATEDIFF(day, b.last_found, GETDATE()) > 30
GROUP BY a.branch;