CODEWITHSUNDEEP
mysqlsql
Ashish Jagtap
Ashish Jagtap

Reputation: 2819

How can I make more than one select queries in single select query.

I have to write a query where, I need to fetch records for last week, last month, and for all. For this problem I wrote 3 diffrent queries (for last week, for last month and for all)

For Weekly Info :-

SELECT bu.brand_name AS 'Brand_Name',COUNT(s.unique) AS '# Item Sold',SUM(s.price) AS 'Total_Price'
FROM item_details s
LEFT JOIN sales_order o ON s.fk_sales_order = o.id_sales_order 
LEFT JOIN customer_info AS c ON o.fk_customer_id = c.id_customer
LEFT JOIN simple_details cc ON s.unique = cc.unique
LEFT JOIN config_details  cf ON cc.fk_config_id = cf.config_id 
LEFT JOIN brand_details  cb ON cf.fk_brand_id = cb.brand_id 
LEFT JOIN category_details  ctc ON cf.fk_category_id = ctc.category_id 
LEFT JOIN gender_details  g ON cf.fk_gender_id = g.gender_id
LEFT JOIN buyers AS bu  ON bu.brand_name =  cb.name AND bu.category_name = ctc.name AND bu.gender = g.name
WHERE  bu.buyers = 'xyz'  AND DATE_FORMAT(o.created_date,'%Y-%m-%d') >= @weekstartdate AND  DATE_FORMAT(o.created_date,'%Y-%m-%d') <= @weekenddate
GROUP BY bu.brand_name

For Monthly Info :- 

SELECT bu.brand_name AS 'Brand_Name',COUNT(s.unique) AS '# Item Sold',SUM(s.price) AS 'Total_Price'
FROM item_details s
LEFT JOIN sales_order o ON s.fk_sales_order = o.id_sales_order 
LEFT JOIN customer_info AS c ON o.fk_customer_id = c.id_customer
LEFT JOIN simple_details cc ON s.unique = cc.unique
LEFT JOIN config_details  cf ON cc.fk_config_id = cf.config_id 
LEFT JOIN brand_details  cb ON cf.fk_brand_id = cb.brand_id 
LEFT JOIN category_details  ctc ON cf.fk_category_id = ctc.category_id 
LEFT JOIN gender_details  g ON cf.fk_gender_id = g.gender_id
LEFT JOIN buyers AS bu  ON bu.brand_name =  cb.name AND bu.category_name = ctc.name AND bu.gender = g.name
WHERE  bu.buyers = 'xyz'  AND DATE_FORMAT(o.created_date,'%Y-%m-%d') >= @monthstartdate AND  DATE_FORMAT(o.created_date,'%Y-%m-%d') <= @monthenddate
GROUP BY bu.brand_name

For All Records :-

SELECT bu.brand_name AS 'Brand_Name',COUNT(s.unique) AS '# Item Sold',SUM(s.price) AS 'Total_Price'
FROM item_details s
LEFT JOIN sales_order o ON s.fk_sales_order = o.id_sales_order 
LEFT JOIN customer_info AS c ON o.fk_customer_id = c.id_customer
LEFT JOIN simple_details cc ON s.unique = cc.unique
LEFT JOIN config_details  cf ON cc.fk_config_id = cf.config_id 
LEFT JOIN brand_details  cb ON cf.fk_brand_id = cb.brand_id 
LEFT JOIN category_details  ctc ON cf.fk_category_id = ctc.category_id 
LEFT JOIN gender_details  g ON cf.fk_gender_id = g.gender_id
LEFT JOIN buyers AS bu  ON bu.brand_name =  cb.name AND bu.category_name = ctc.name AND bu.gender = g.name
WHERE  bu.buyers = 'xyz'  
GROUP BY bu.brand_name

and these are working fine (giving currect output). But problem is that, I have to merge these three queries in single one. Where output should be as Brand name, item_sold(week), total_price(week),item_sold(month), total_price(month),item_sold(all), total_price(all) How can I write this query?

Upvotes: 2

Views: 211

Answers (4)

Kondybas
Kondybas

Reputation: 696

If all three selects uses the same fields in the results, you can UNION them:

SELECT * 
   FROM  (SELECT 1) AS a
   UNION (SELECT 2) AS b
   UNION (SELECT 3) AS c

If you need to tell week/mon/all records from each other - just add constant field containing "week" or "mon"

Upvotes: 1

Andomar
Andomar

Reputation: 238058

You could use case to limit aggregates to a subset of rows:

select  bu.brand_name
,       count(case when date_format(o.created_date,'%Y-%m-%d') >= @weekstartdate
             and date_format(o.created_date,'%Y-%m-%d') <= @weekenddate 
             then 1 end) as '# Item Sold Week'
,       sum(case when date_format(o.created_date,'%Y-%m-%d') >= @weekstartdate
             and date_format(o.created_date,'%Y-%m-%d') <= @weekenddate 
             then s.price end) as 'Total_Price Week'
,       count(case when date_format(o.created_date,'%Y-%m-%d') >= @monthstartdate
             and date_format(o.created_date,'%Y-%m-%d') <= @monthstartdate
             then 1 end) as '# Item Sold Month'
,       ...

Upvotes: 2

penartur
penartur

Reputation: 9912

Without looking deep into your code, the obvious solution would be

SELECT
    all.brand_name
    pw.items_sold items_sold_week
    pw.total_price total_price_week
    pm.items_sold items_sold_month
    pm.total_price total_price_month
    all.items_sold items_sold_all
    all.total_price total_price_all
FROM
    (your all-time select) all
    JOIN (your per-month select) pm ON all.brand_name = pm.brand_name
    JOIN (your per-week select) pw ON all.brand_name = pw.brand_name

Though you probably should rethink your entire approach and make sure whether you really want that kind of logic in a DB layer or it is better to be in your application.

Upvotes: 4

Nigel Coates
Nigel Coates

Reputation: 46

You cam use the UNION.keyword between the queries to bundle them.together BUT tje column types and sequence must be the same in all queries. You could add an identifier to each set

Upvotes: 0

Related Questions