Wednesday, March 21, 2012

Rewrite a query efficiently

Is there a efficient way to write this query?

SELECT CASE
WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END AS Population_Range,
COUNT(CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END) AS [No. Of Countries]
FROM Country
GROUP BY
CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' ENDThe CASE statement embedded in you COUNT function is unnecessary:
SELECT CASE
WHEN Population BETWEEN 0 AND 100 THEN '0-100'
WHEN Population BETWEEN 101 AND 1000 THEN '101-1000'
ELSE 'Greater than 1000' END AS Population_Range,
COUNT(*) AS [No. Of Countries]
FROM Country
GROUP BY CASE
WHEN Population BETWEEN 0 AND 100 THEN '0-100'
WHEN Population BETWEEN 101 AND 1000 THEN '101-1000'
ELSE 'Greater than 1000' END

No comments:

Post a Comment