Mohit Shrivastava Mohit Shrivastava - 3 months ago 11
MySQL Question

How to combine 3 queries?

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.

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.