How to merge the result of two select mysql query

Question

my first query is

select GEAR,count(GEAR) 
from new_failure 
where STN_CODE = "BVH" group by(Gear);

and its result is

result if image is not visible

# GEAR  Total
SIGNAL  8
POINT   16
HASSDAC 5
,SIGNAL 1
SSDAC   1
TRACK CIRCUIT   9
UFSBI   2
DC  1

2nd query

select GEAR,count(GEAR) 
from new_failure 
where STN_CODE = "BVH"  
and MONTH(fail_time) = 4 
group by(Gear);

result

# GEAR  April
SIGNAL  3
POINT   4
HASSDAC 1
,SIGNAL 1
SSDAC   1

i want result in the form given in image below

please check this https://stackoverflow.com/questions/10538539/join-two-select-statement-results — Dinesh s, Sep 14 '21 at 10:35
select t1.Gear, t1.[This],COALESCE(t2.[April],0) AS [ April] from (select GEAR,count(GEAR) as "This" from new_failure where STN_CODE = "BVH" group by(Gear)) t1 LEFT JOIN (select GEAR,count(GEAR) as "April" from new_failure where STN_CODE = "BVH" and MONTH(fail_time) = 4 group by(Gear)) t2 on (t1.Gear = t2.Gear); my query looks something like this but getting error at t1.[this ] "[" is not valid — Himanshu rana, Sep 14 '21 at 10:50

score -1 · Accepted Answer · answered Sep 14 '21 at 10:46

-1

You can use either LEFT JOIN, RIGHT JOIN or JOIN depending on what you are aiming to get,

SELECT * 
FROM ( select GEAR,count(GEAR) 
       from new_failure 
       where STN_CODE = "BVH" group by(Gear) AS A
JOIN ( select GEAR,count(GEAR) 
       from new_failure 
       where STN_CODE = "BVH"  
       and MONTH(fail_time) = 4 AS B
ON A.orders_id=B.orders_id

or you can refer to this link for a similar question joining two select statements

answered Sep 14 '21 at 10:46

Mamo

72
4

This solution is unnecessarily overcomplicated as both queries select data from the same table and group by the same field. The only difference is what to count, which can be done via conditional counting. A single pass on the table, no joins. – Shadow Sep 14 '21 at 12:21

How to merge the result of two select mysql query

1 Answers1