Join select statements to get columns in SQL

Question

SELECT COUNT(Type) from House where Type = 1
SELECT COUNT(Type) from House where Type = 2
SELECT COUNT(Type) from House where Type = 3

My question is: I want to join the above 3 statements to get: 3 columns i.e. eg: ColumnType1: '50', ColumnType2: '60', columnType3: '45'

thanks

Answer 1

You can create the columns using an aggregate function with a CASE expression:

SELECT 
  count(case when Type = 1 then Type end) as type_1,
  count(case when Type = 2 then Type end) as type_2,
  count(case when Type = 3 then Type end) as type_3
from House