CODEWITHSUNDEEP
sqlsql-servert-sql
Mao
Mao

Reputation: 41

combining columns with different queries sql

I have 3 queries, but I want to combine them into one (with three columns). this are my results

this is my code:

Select family = count(s.destiny) from rental
inner join cars as s on s.number_car = ID_car 
where s.destiny in (select destiny from cars where destiny like 'kids') 

Select lux = count(s.destiny) from rental
inner join cars as s on s.number_car = ID_car
where s.destiny in (select destiny from cars where destiny like 'luxury')

Select sports = count(s.destiny) from rental
inner join cars as s on s.number_car = ID_car
where s.destiny in (select destiny from cars where destiny like 'sport car')

Can you help me combine it into one 'select'?

Upvotes: 2

Views: 67

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28900

why you cant try this ?

Select destiny,count(*) from rental
inner join cars as s on s.number_car = ID_car
where s.destiny in ( 'cars','luxury')
group by destiny

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Conditional aggregation seems like this right approach:

Select sum(case when density = 'kids' then 1 else 0 end) as family,
       sum(case when density = 'lux' then 1 else 0 end) as luxury,
       sum(case when density = 'sport car' then 1 else 0 end) as sports       
from rental r inner join
     cars s
     on s.number_car = ID_car ;

Upvotes: 3

jhilden
jhilden

Reputation: 12429

The simple answer is:

SELECT 
(
Select family = count(s.destiny) from rental
inner join cars as s on s.number_car = ID_car 
where s.destiny in (select destiny from cars where destiny like 'kids') 
) as kids,
(
Select lux = count(s.destiny) from rental
inner join cars as s on s.number_car = ID_car
where s.destiny in (select destiny from cars where destiny like 'luxury')
) as luxury,
(
Select sports = count(s.destiny) from rental
inner join cars as s on s.number_car = ID_car
where s.destiny in (select destiny from cars where destiny like 'sport car')
) as sportsCar

That being said, I strongly recommend considering removing the sub-queries here.

Something like this:

SELECT destiny, COUNT(1)
FROM cars
GROUP BY destiny

Upvotes: 0

Related Questions