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
Wednesday, March 21, 2012
Rewrite a query efficiently
Labels:
0-100,
case,
database,
efficient,
efficiently,
microsoft,
mysql,
oracle,
population,
query,
queryselect,
rewrite,
server,
sql,
write
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment