Month-Year-wise product sales query giving different results on query optimization
I want to retrieve sum(Quantity) of products ordered by month-year wise. For example, the output looks like:
product1........20......july-2008
product2........10......july-2008
Below is my default query, which seems to be giving correct results:
SELECT ITEMNAME, SUM(QTY), DATE_FORMAT(BILLDATE,'%M-%Y') FROM BILLITEMS
GROUP BY BILLDATE, ITEMNAME
ORDER BY YEAR(BILLDATE), MONTH(BILLDATE), ITEMNAME;
My concern is query speed. I tried following optimizations, but it is giving different results:
SELECT ITEM开发者_如何学CNAME, SUM(QTY), DATE_FORMAT(BILLDATE,'%M-%Y') FROM BILLITEMS
GROUP BY BILLDATE, ITEMNAME
ORDER BY DATE_FORMAT(BILLDATE,'%Y-%M'), ITEMNAME;
and another one:
SELECT ITEMNAME, SUM(QTY), DATE_FORMAT(BILLDATE,'%M-%Y') FROM BILLITEMS
GROUP BY DATE_FORMAT(BILLDATE,'%M-%Y'), ITEMNAME
ORDER BY DATE_FORMAT(BILLDATE,'%Y-%M'), ITEMNAME;
精彩评论