Why query not returning result and how to add thousand separator in sales column

I am trying to retrieve results from this query, but it is not fetching any record:

SELECT DATE_FORMAT(BILLDATE,'%M-%Y'), SUM(GROSSAMOUNT), SUM(NETAMOUNT)
FROM BILLDETAILS
WHERE DATE_FORMAT(BILLDATE,'%M-%Y') BETWEEN 'May-2011' AND 'Jul-2011'
GROUP BY MONTH(BILLDATE), YEAR(BILLDATE)
ORDER BY YEAR(BILLDATE) DESC, MONTH(BILLDATE);

I also want to display SUM(GrossAmo开发者_运维技巧unt) and Sum(NetAmount) as, for example, 1,10,20,356

---

Your query is doing a string comparison, not a date comparison. Try something like this instead:

SELECT DATE_FORMAT(BILLDATE,'%M-%Y'), SUM(GROSSAMOUNT), SUM(NETAMOUNT)
FROM BILLDETAILS
WHERE BILLDATE BETWEEN '2011-05-01' AND '2011-07-01'
GROUP BY MONTH(BILLDATE), YEAR(BILLDATE)
ORDER BY YEAR(BILLDATE) DESC, MONTH(BILLDATE);

There are a variety of ways to specify the dates in the WHERE clause. Have a look at the Date and Time Functions reference for alterntives. Example:

...
WHERE BILLDATE BETWEEN '2011-05-01' AND '2011-05-01' + INTERVAL 2 MONTH
...

You may find the STR_TO_DATE function useful for converting strings to dates:

SELECT STR_TO_DATE('May-2011', '%M-%Y');
+----------------------------------+
| STR_TO_DATE('May-2011', '%M-%Y') |
+----------------------------------+
| 2011-05-00                       |
+----------------------------------+

So your WHERE clause could be:

WHERE BILLDATE BETWEEN STR_TO_DATE('May-2011', '%M-%Y')
                   AND STR_TO_DATE('Jul-2011', '%M-%Y')

To format the value, use the FORMAT() function:

SELECT FORMAT('11020356', 0);
+-----------------------+
| FORMAT('11020356', 0) |
+-----------------------+
| 11,020,356            |
+-----------------------+

...although formatting is usually best left to the application. If you format it in MySQL, processing it in the application might become a bit tricky.

---

SELECT DATE_FORMAT(BILLDATE,'%M-%Y'), SUM(GROSSAMOUNT), SUM(NETAMOUNT)
FROM BILLDETAILS
where billdate between '2011-05-01' and '2011-07-31'
GROUP BY MONTH(BILLDATE);

if you're looking for records within 3 months in the same year you don't need year() function.

You can also take a look at format() function

---

Your problem is that you compare dates as strings in WHERE. If you can't use dates there (why?) you have to format the dates at least the way which works, ie in yyyymm format:

WHERE DATE_FORMAT(BILLDATE,'%Y%m') BETWEEN '201105' AND '201107'