CODEWITHSUNDEEP
mysql
prem gupta
prem gupta

Reputation: 69

Combine two order by queries to get result

I have two queries :

SELECT COUNT(*) as adet, MONTHNAME(date_created) as monthname
FROM orders 
WHERE YEAR(date_created) = 2017
GROUP BY MONTH(date_created)

which returns this result 

adet monthname 
----------------
17   January
24   February
45   March 
40   April

and another query :

SELECT COUNT(*) as adet, seller_id
FROM orders 
WHERE YEAR(date_created) = 2017
GROUP BY seller_id

which returns this result :

adet seller_id
---------------
120  20
120  21
500  22
400  23

How can I combine those two queries to get the result like this:

adet month seller_id
--------------------
-09- -Jan- --20--
-05- -Fab- --20--
-05- -Mar- --20--
...
-05- -jan- --21--
-04- -Fab- --21--
...
-05- -jan- --22--
-04- -Fab- --22--

Upvotes: 2

Views: 47

Answers (2)

O. Jones
O. Jones

Reputation: 108641

You can try 

 SELECT COUNT(*) adet, LAST_DAY(date_created) month_ending, seller_id
   FROM orders
  WHERE date_created >= DATE_FORMAT(CURDATE(),'%Y-01-01')
    AND date_created < DATE_FORMAT(CURDATE(),'%Y-01-01') + INTERVAL 1 YEAR
  GROUP BY LAST_DAY(date_created), seller_id

Why would you use something so seemingly complex? Because

  1. it will work next year.
  2. it will handle ranges of dates which don't end on year boundaries
  3. it will be able to use an index on your date_created column and therefore run faster.

Upvotes: 3

chirag satapara
chirag satapara

Reputation: 1937

Try below query:

SELECT COUNT(*) as adet, MONTHNAME(date_created) as monthname,seller_id
FROM orders where YEAR(date_created) = 2017
GROUP BY MONTH(date_created),seller_id

Upvotes: 1

Related Questions