Filter Data By month Mysql

Question



    $listSQL = "SELECT op.name as prodname,count(*) total 
                FROM oc_order_product op 
                INNER JOIN oc_order o ON op.order_id = o.order_id 
                INNER JOIN oc_product_to_category p2c ON op.product_id = p2c.product_id 
                INNER JOIN oc_category_description cd ON cd.category_id = p2c.category_id ";
    $listSQL = $listSQL."where lower(cd.name) LIKE '%".$category_name."%' 
                         AND YEAR(o.date_added) = '".$StartDate."' 
                         AND o.order_status_id > '0' ";
    $listSQL = $listSQL."GROUP BY op.name ORDER BY o.date_added ASC";

I have this query where i am displaying product names and count by year.

I want to display Product name, and for each product, show month and count for that month for that particular year.

for example for year 2015, show all 12 months and under which show count of products for that month.

Thanks

`group by year(datefield), month(datefield)`, basically, assuming your dates are stored as actual date/time fields. — Marc B, Jun 28 '16 at 21:51
Regarding the statement _"I have this query where i am displaying product names and count by year"_; that is not what the query you've shown does. It filters by year in the WHERE clause, but the ORDER BY will use an effectively random `date_added` from the rows found for each grouped value. — Uueerdo, Jun 28 '16 at 21:55
dates are stored as '2016-06-28 11:58:01' and if i group by month, it is showing product name month name and repeating itself, i am not getting count for each month. — Syed Mudabbir, Jun 28 '16 at 21:56
@Uueerdo I needed to actually sort by month but since i cant get it to work, i did order by date_added I changed it to order by total(count). and I get the output Product name, Total. — Syed Mudabbir, Jun 28 '16 at 22:02
@SyedMudabbir use count(distinct total) as cntTotal instead of count(*) total — rahul, Jun 28 '16 at 22:03
@SyedMudabbir And if you are trying to get the sum of quantities and sum of amount of each month then please refer http://stackoverflow.com/questions/2436284/mysql-sum-for-distinct-rows?rq=1 — rahul, Jun 28 '16 at 22:07

score 0 · Answer 1 · answered Jun 28 '16 at 22:01

Your original query looks pretty close, but using non-grouped, non-aggregated fields "outside" a group by is usually not a good idea unless they came from a table whose full primary key was part of the group by list. That said, this should give you what you want.

SELECT op.name AS prodname
, YEAR(o.date_added) AS oYear
, MONTH(o.date_added) AS oMonth
, COUNT(DISTINCT o.order_id) AS numOfOrders
FROM ...
GROUP BY prodname, oYear, oMonth
ORDER BY prodname, oYear, oMonth
;

[...] being your original FROM and WHERE

Filter Data By month Mysql

1 Answers1