Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in Data Science by (17.6k points)

I try to combine these two queries in one.

the result of these queries is the number of accepted / rejected applications for a given operator.

I want to get such a result - in three column: number of accepted applications , number of rejected applications and operators assigned to it.

select count(applications.id) as number_of_applications, operator_id

from applications

inner join travel p on applications.id = p.application_id

inner join trip_details sp on p.id = sp.trip_id

where application_status ilike '%rejected%'

group by  operator_id

order by number_of_applications desc;

select count(applications.id) as number_of_applications, operator_id

from applications

inner join travel p on applications.id = p.application_id

inner join trip_details sp on p.id = sp.trip_id

where application_status ilike '%accepted%'

group by  operator_id

order by number_of_applications desc;

1 Answer

0 votes
by (41.4k points)

By using conditional aggregation you can add the ordering that you prefer:

select

  sum(case when application_status ilike '%accepted%' then 1 else 0 end) as number_of_applications_accepted,

  sum(case when application_status ilike '%rejected%' then 1 else 0 end) as number_of_applications_rejected, 

  operator_id

from applications

inner join travel p on applications.id = p.application_id

inner join trip_details sp on p.id = sp.trip_id

where (application_status ilike '%rejected%') or (application_status ilike '%accepted%')

group by operator_id;

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...