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

Question

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?

penartur · Accepted Answer · 2012-06-28 07:31:39Z

4

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.

answered Jun 28, 2012 at 7:31

penartur

9,9325 gold badges41 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ashish Jagtap Over a year ago

:- when i run this query it dose not show any record in result.

Ashish Jagtap Over a year ago

the problem in this query is that when one of the sub-query returns no records then all result will return no records

Brent Baisley Over a year ago

You probably want to change it to LEFT JOINs to solve the no records problem.

Ashish Jagtap Over a year ago

@Brent Baisley yes it is working fine with 'LEFT JOIN' but Andomar solution is faster than this thank you so much for you'r help

Andomar · Accepted Answer · 2012-06-28 07:33:28Z

2

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'
,       ...

answered Jun 28, 2012 at 7:33

Andomar

239k55 gold badges387 silver badges412 bronze badges

8 Comments

Ashish Jagtap Over a year ago

thanks it is working fine for '# of item sold Week' and '# of item sold Month' but it is showing null for 'Total_Price Week' and 'Total_Price Month'

Andomar Over a year ago

That's strange, are you sure you're using then s.price end for the prices and then 1 end for the item counts?

Ashish Jagtap Over a year ago

SUM(CASE WHEN DATE_FORMAT(o.created_date,'%Y-%m-%d') >= '2012-06-25' AND DATE_FORMAT(o.created_date,'%Y-%m-%d') <= '2012-06-28' THEN s.price END) AS 'Total_Price Week',

Andomar Over a year ago

That should work. If you run the query without group by, does s.price contain non-null values for that range?

Ashish Jagtap Over a year ago

when i run query without group by clause then it shows only one record with null value for that both columns.

|

Kondybas · Accepted Answer · 2012-06-28 08:07:10Z

1

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"

edited Jun 28, 2012 at 8:07

answered Jun 28, 2012 at 7:30

Kondybas

7166 silver badges17 bronze badges

Comments

Nigel Coates · Accepted Answer · 2012-06-28 07:31:05Z

0

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

answered Jun 28, 2012 at 7:31

Nigel Coates

462 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related