USING OF SUBQUERY

Question

below is database and table details. database name - mis_v1 table 1 name - trips table 2 name - client

i tried below queries

Query 1

select cl.client_name CLIENT
     , count(t.trip_type) TRIPS
     , count(distinct t.vehicle_reg_no) VEHICLES
  from mis_v1.trips t
  JOIN mis_v1.client cl 
    ON cl.id = t.client_id 
 group 
    by cl.client_name;

Query 1 result

CLIENT      TRIPS   VEHICLES
anz-ABlr    118     16  
citrix-CBlr 159     15  
dxc-DBlr    26      5   
Eps-Blr     116     24  
goc-GocHyd  191     10
Unisys-BLR  192     55
Wipro-Ncr   86      33
Wipro-Pnq   10      5

Query 2

select cl.client_name CLIENT
     , count(t.trip_delay_reason) LATE_TRIPS
  FROM mis_v1.trips t
  JOIN mis_v1.client cl 
    ON cl.id = t.client_id
 where t.trip_delay_reason = "DRIVER"
 group 
    by cl.client_name;

Query 2 result

CLIENT       LATE_TRIPS
anz-ABlr        53
citrix-CBlr     25
dxc-DBlr        1
Wipro-Ncr       1
goc-GocHyd      17

I need result as below

CLIENT     TRIPS    VEHICLES    LATE_TRIPS
anz-ABlr    118     16          53
citrix-CBlr 159     15          25
dxc-DBlr    26      5           1
Eps-Blr     116     24          -
goc-GocHyd  191     10          17
Unisys-BLR  192     55          -
Wipro-Ncr   86      33          1
Wipro-Pnq   10      5           -

Kindly give me solution.Thanks in advance

Answer 1

If I understood you correctly, you need something like this:

select cl.client_name CLIENT
     , count(t.trip_type) TRIPS
     , count(distinct t.vehicle_reg_no) VEHICLES
     , coalesce(cast(count(case when t.trip_delay_reason = "DRIVER" then 1 else null end) as char), '-') LATE_TRIPS
from mis_v1.trips t
JOIN mis_v1.client cl 
  ON cl.id = t.client_id 
group 
  by cl.client_name;

So, please read more about COUNT aggreagate function and probably CASE operator