How to combine 3 queries?

Question

I have 2 tables and I am trying to pull the records for material specific. So my queries are as follows

Single Day Query

SELECT tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin, COUNT( product_serial_no ) AS  `S_FTD` 
FROM register_product_claim
INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
WHERE retailer_code =  '9017'
AND register_product_claim.dt =  '2016-08-04'
GROUP BY tm.asin
ORDER BY register_product_claim.dt DESC 
LIMIT 0 , 30

Month Wise

SELECT tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin, COUNT( product_serial_no ) AS  `S_MTD` 
FROM register_product_claim
INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
WHERE retailer_code =  '9017'
AND MONTH( register_product_claim.dt ) =  '08'
GROUP BY tm.asin
ORDER BY register_product_claim.dt DESC 
LIMIT 0 , 30

Qtr Wise

SELECT tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin, COUNT( product_serial_no ) AS  `S_QTD` 
FROM register_product_claim
INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
WHERE retailer_code =  '9017'
AND (
MONTH( register_product_claim.dt ) =  '08'
OR MONTH( register_product_claim.dt ) =  '07'
OR MONTH( register_product_claim.dt ) =  '06'
)
GROUP BY tm.asin
ORDER BY register_product_claim.dt DESC 
LIMIT 0 , 30

This all works fine.

Now I am trying to merge all the queries. but unable to merge them I used. Union, Joins, but no luck.

the last one tried was

SELECT *
FROM 
    (SELECT 
        tm.internal_name AS model_number, tm.item_description, tm.mrp, tm.asin,
        COUNT(product_serial_no) as S_FTD
    FROM
        register_product_claim
    INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
    WHERE
        retailer_code = '9017'
            AND register_product_claim.dt = '2016-08-04'
    GROUP BY tm.asin) as s1  
   Inner Join
    (SELECT 

        COUNT(product_serial_no) as S_MTD
    FROM
        register_product_claim
    INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
    WHERE
        retailer_code = '9017'
            AND MONTH(register_product_claim.dt) = '08'
    GROUP BY tm.asin) as s2
   Inner Join
    (SELECT 

        COUNT(product_serial_no) as S_QTD
    FROM
        register_product_claim
    INNER JOIN tbl_model tm ON register_product_claim.model_number = tm.asin
    WHERE
        retailer_code = '9017'
            AND (MONTH(register_product_claim.dt) = '08'
            OR MONTH(register_product_claim.dt) = '07'
            OR MONTH(register_product_claim.dt) = '06')
    GROUP BY tm.asin) as s3

My Expected output is

model_number | item_description |  mrp | asin | S_FTD | S_MTD | S_QTD

Where S_FTD is one day, S_MTD is Monthly and S_QTD is Quarterly results.

sagi · Accepted Answer

You can do it with conditional aggregation, adjust this to your code:

SELECT t.model_number,t.item_description,t.mrp,t.asin,
       MAX(CASE WHEN t.new_col = 'D' then t.S_TD END) as S_FTD,
       MAX(CASE WHEN t.new_col = 'M' then t.S_TD END) as S_MTD,
       MAX(CASE WHEN t.new_col = 'Q' then t.S_TD END) as S_QTD
FROM (
     SELECT ss.*,'D' as new_col FROM (Daily Query) ss
     UNION ALL
     SELECT ss1.*,'M' FROM (Monthly Query) ss1
     UNION ALL
     SELECT ss2.*,'Q' FROM (Quarter Query) ss2
     ) t
GROUP BY t.model_number,t.item_description,t.mrp,t.asin

Note: You have to alias the column S_FTD in the daily query as S_TD for this to work.

How to combine 3 queries?

Answers (2)

Related Questions