Write a query to display number of available Ac and Non Ac buses . Give alias name to type as bus_type and count of buses as bus_count.
Answers
Answer:
Explanation:
BUSES
----------------
|BUS_NO
|BUS_NAME
|TYPE
|AVAIL_SEAT
I have to calculate how many AC and NON AC buses are there?
My query for count is:
count(*)bus_count
sum(case when type='ac' then 1 else 0)
sum (case when type="non ac" then 1 else 0)
from buses
group by bus_number;
sql
1
Since the content inside a database table are case sensitive adding lower or upper function will convert the content of the type attribute to lower or upper case and will try to match it with the value given. Here I have used lower function but upper function can also be used then we'll have to give the type value as 'AC'/'NON AC'.
SELECT
type AS bus_type,
CASE
WHEN LOWER(type) = 'ac' THEN COUNT(type)
WHEN LOWER(type) = 'non ac' THEN COUNT(type)
END AS bus_count
FROM
buses
Assuming there are only 2 values in 'type' column, following query will work:
Select type,
Count (*) as bus_count
From bus
Group by 1;
shareimprove this answerfollow
answered Aug 20 '17 at 6:11
Hemant
42922 gold badges44 silver badges1414 bronze badges
add a comment
0
If you need the two result on the same column you a need only a sum as
select
sum( case when type ='ac' then 1 else 0 end ) type_ac
, sum( case when type <>'ac' then 1 else 0 end ) type_not_ac
from buses
if you need a list for type the Hemant answer is what you need
select type as bus_type,
case when type='ac' then count(type)
when type='non ac' then count(type) end bus_count
from buses group by type
Answer:select type as bus_type,count(bus_no) as bus_count
from buses
group by type
order by bus_count desc;
Explanation:
we select the type and bus_no and assign them the given alias and we group the bus by the distinctive type and sort them by the bus_count in decreasing order as asked in question