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.stack.imgur.com/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.stack.imgur.com/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!!

>Solution :

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;