CODEWITHSUNDEEP
sqlt-sql
Moh Najdawi
Moh Najdawi

Reputation: 103

Join select statements to get columns in SQL

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

Upvotes: 3

Views: 101

Answers (4)

Vince Bowdren
Vince Bowdren

Reputation: 9208

There is a cleaner type of SQL which can give you this answer, but you will have each type on a different row:

SELECT Type, COUNT(Type) FROM House GROUP BY Type

It has the disadvantage of not giving you columns as you asked for; but the advantage is that it works for any number of different types without needing to change the query.

Upvotes: 1

Kuzgun
Kuzgun

Reputation: 4737

SELECT
    COUNT(Type) as val1,
    (SELECT COUNT(Type) from House where Type = 2) as val2,
    (SELECT COUNT(Type) from House where Type = 3) as val3
from House where Type = 1

Upvotes: 0

Taryn
Taryn

Reputation: 247840

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

Upvotes: 3

juergen d
juergen d

Reputation: 204864

You can use a case and add up if the Type matches

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

Upvotes: 1

Related Questions