SQL – Count rows based on matching columns and value range

Please see below query using T-SQL with SSMS. There are three tables: B, G and L.

B has a column Bname
G has 2 columns Gname, Gross
L has 2 columns Bname, Gname

Gross column is an INT ranging values between 80 and 100.
Table L’s columns: Bname and Gname will feature names from the B and G tables on the same row. Where both names feature on the same row, I would like to COUNT this as one item; only if the Gross on Table G ranges between 80 and 100 to the corresponding Gname row.
My current query reads:

SELECT l.bname, (SELECT COUNT(*) FROM g WHERE g.gross BETWEEN 80 AND 90) AS Good  
FROM l  
INNER JOIN b  
ON b.bname=l.bname  
INNER JOIN g  
ON g.gname=l.gname  
GROUP BY l.bname;

The result is nearly there, but it counts all Table G:Gname rows betweeen 80 and 100. Emitting the instances on Table L where the Bname and Gname are on the same row.

Thanks in advance for looking.

Answer

I suspect that you want:

SELECT l.bname,
       (SELECT COUNT(*)
        FROM b INNER JOIN
             g  
             ON g.gname = l.gname 
        WHERE b.bname = l.bname AND g.gross BETWEEN 80 AND 90
       ) AS Good  
FROM l ;

The outer aggregation is not needed of l.bname is unique.

This would more commonly be calculating using conditional aggregation:

SELECT l.bname, 
       SUM(CASE WHEN g.gross BETWEEN 80 AND 90 THEN 1 ELSE 0 END) AS Good  
FROM l INNER JOIN
     b  
     ON b.bname = l.bname INNER JOIN
     g  
     ON g.gname = l.gname  
GROUP BY l.bname;

No subquery is needed.