How to join two queries to get result in single query

Question

I have two queries. From 1st one I am getting selected year and month data and from the second query I am getting the data of previous year i.e for the last year

query1: SELECT sum(a.netamount) NETAMOUNT,e.CUSTOMERDESCRIPTOR,YEAR(BILLDATE) AS DATEYEAR, MONTH (BILLDATE) DATEMONTH FROM syncbill a,ecustomer e WHERE a.OUTLET=e.CUSTOMERIDENTIFIER and YEAR(BILLDATE) = '2018' AND MONTH(BILLDATE) = '09' group by outlet

query2: SELECT sum(a.netamount) NETAMOUNT,e.CUSTOMERDESCRIPTOR,YEAR(BILLDATE) AS DATEYEAR, MONTH (BILLDATE) DATEMONTH FROM syncbill a,ecustomer e WHERE a.OUTLET=e.CUSTOMERIDENTIFIER and YEAR(BILLDATE) = '2018-1' AND MONTH(BILLDATE) = '09' group by outlet

I am using MySQL 5.5 and putting year-1 to get previous year data like 2018-1 gives 2017.

Everything is common between these two queries except the year in query1 year is 2018 and in query2 year is 2018-1 i.e 2017.

So I want to join both the queries so that I can get data in single run.

because there is no data in 2017 that's why it is showing blank, but I want to show 0 instead.

I know I can get this with joins but have very limited experience in that.

Answer 1

SELECT sum(a.netamount) NETAMOUNT
,e.CUSTOMERDESCRIPTOR
,YEAR(BILLDATE) AS DATEYEAR
, MONTH (BILLDATE) DATEMONTH 
FROM syncbill a 
INNER JOIN ecustomer e ON a.OUTLET=e.CUSTOMERIDENTIFIER  
    and YEAR(BILLDATE) = '2018' AND MONTH(BILLDATE) = '09' 
group by outlet

UNION ALL

SELECT sum(a.netamount) NETAMOUNT
,e.CUSTOMERDESCRIPTOR,
YEAR(BILLDATE) AS DATEYEAR
, MONTH (BILLDATE) DATEMONTH 
FROM syncbill a
INNER JOIN ecustomer e ON  a.OUTLET=e.CUSTOMERIDENTIFIER 
    and YEAR(BILLDATE) = '2018-1' AND MONTH(BILLDATE) = '09' 
group by outlet

If you want two separated rows you could use union all

UNION ALL return always both the row result

You could also use a single query group by year

    SELECT sum(a.netamount) NETAMOUNT
    ,e.CUSTOMERDESCRIPTOR
    ,YEAR(BILLDATE) AS DATEYEAR
    , MONTH (BILLDATE) DATEMONTH 
    FROM syncbill a 
    INNER JOIN ecustomer e ON a.OUTLET=e.CUSTOMERIDENTIFIER  
        and YEAR(BILLDATE) IN ('2018', '2018-1') AND MONTH(BILLDATE) = '09' 
    group by outlet, DATEYEAR, DATEMONTH