CODEWITHSUNDEEP
sql
dani
dani

Reputation: 27

SQL counting with condition

If I have a table called Buildings.

 Room_No    Bldg Capacity 
    112     SCEN   23
    242     JBHT   25
    542     SCEN    4
    324     JBHT   24

What I want is to print out the Bldg name and the total number of rooms that have a capacity more than 20 in each building. So it is supposed to look like:

 Bldg   Total
  SCEN    1
  JBHT    2

Am I going on the right track: 

 Select Bldg, Count(Capacity > 20) as Total from Buildings Group By Total Desc

Upvotes: 2

Views: 60

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could use CASE:

Select Bldg, Count(CASE WHEN Capacity > 20 THEN 1 END) as Total 
from Buildings 
Group By Bldg
ORDER BY Total DESC;

If you are using Postgresql you could rewrite it as:

Select Bldg, Count(1) FILTER(WHERE Capacity>20) as Total 
from Buildings 
Group By Bldg
ORDER BY Total DESC;

Rextester Demo

Upvotes: 3

zambonee
zambonee

Reputation: 1647

The other answers seem overly complicated for this problem. The solution is rather simple:

SELECT Bldg, COUNT(*) AS count 
FROM Buildings 
WHERE Capacity > 20 
GROUP BY Bldg

Here is the Fiddle: http://sqlfiddle.com/#!9/308a6/1

Upvotes: 1

Related Questions