How can I calculate a column data for different conditions using single query in sql server?

I am using below query to count a particular column data based on single condition.

SELECT COUNT(DISTINCT(S.INVOICENO)) AS LESSTWO , YEAR开发者_Python百科(S.invoicedate) YER, 
    Month(S.invoicedate) MNTH
FROM SALESDATA S 
where S.BILLINGTYPE='INVOICE' 
    AND (S.invoicedate >='4-1-2009' and S.invoicedate <='4-30-2010')
    AND S.TAXABLEAMT <=2000
GROUP BY YEAR(S.invoicedate) ,Month(S.invoicedate) 
ORDER BY YEAR(S.invoicedate) ,Month(S.invoicedate)

But I have to calculate for 3 more conditions. Is it possible to count based on 3 conditions(i.e. 2001 - 3000,3001 - 4000, 4001 - 5000,>5000) and I want result set for all the conditions?

Regards,

N.SRIRAM

---

Untested, but should be about right:

SELECT CASE
          WHEN S.TaxableAmt <= 2000 THEN '<=2000'
          WHEN S.TaxableAmt <= 3000 THEN '2001-3000'
          WHEN S.TaxableAmt <= 4000 THEN '3001-4000'
          ELSE '>5000'
       END as Bracket,
COUNT(DISTINCT(S.INVOICENO)) AS LESSTWO , YEAR(S.invoicedate) YER, 
    Month(S.invoicedate) MNTH
FROM SALESDATA S 
where S.BILLINGTYPE='INVOICE' 
    AND (S.invoicedate >='20090401' and S.invoicedate <='20100430')
GROUP BY
      CASE
          WHEN S.TaxableAmt <= 2000 THEN '<=2000'
          WHEN S.TaxableAmt <= 3000 THEN '2001-3000'
          WHEN S.TaxableAmt <= 4000 THEN '3001-4000'
          ELSE '>5000'
       END,
YEAR(S.invoicedate) ,Month(S.invoicedate) 
ORDER BY YEAR(S.invoicedate) ,Month(S.invoicedate)

You have to repeat the case expression in the GROUP BY clause (or put it all in a subquery), and I've fixed your date constants so that they're unambiguous.

---

I think you really need to split up the query into 3 distinct queries - one for each of the conditions you require