SQL Server : how to use count

Question

I am using this SQL Server database. I am defining the following remarks for countries

CASE 
    WHEN density_per_sq_km > 1000 THEN 'Overpopulated'
    WHEN density_per_sq_km > 500 THEN 'above average'
    WHEN density_per_sq_km > 250 THEN 'average'
    WHEN density_per_sq_km > 50 THEN 'below average' 
    ELSE 'Underpopulated'
END as remarks

Now I want to count how many countries are there in each remark. How can I do that? I am using the following query but it fails

SELECT 
    COUNT(country) as no_of_countries,
    CASE 
        WHEN density_per_sq_km > 1000 THEN 'Overpopulated'
        WHEN density_per_sq_km > 500 THEN 'above average'
        WHEN density_per_sq_km > 250 THEN 'average'
        WHEN density_per_sq_km > 50 THEN 'below average' 
        ELSE 'Underpopulated'
    END as remarks
FROM 
    countries_by_population 
GROUP BY 
    remarks;

Use `COUNT(distinct country)` – juergen d Jan 13 '16 at 09:27 — juergen d, Jan 13 '16 at 09:27

score 2 · Accepted Answer · answered Jan 13 '16 at 09:28

In the group by clause you cannot use that column alias, use the case expression instead

SELECT
      COUNT(country) AS no_of_countries
    , CASE
            WHEN density_per_sq_km > 1000 THEN 'Overpopulated'
            WHEN density_per_sq_km > 500 THEN 'above average'
            WHEN density_per_sq_km > 250 THEN 'average'
            WHEN density_per_sq_km > 50 THEN 'below average'
            ELSE 'Underpopulated'
      END AS remarks
FROM countries_by_population
GROUP BY
      CASE
            WHEN density_per_sq_km > 1000 THEN 'Overpopulated'
            WHEN density_per_sq_km > 500 THEN 'above average'
            WHEN density_per_sq_km > 250 THEN 'average'
            WHEN density_per_sq_km > 50 THEN 'below average'
            ELSE 'Underpopulated'
      END
;

can you explain is there any possible way i can use the remarks in GROUP BY or ORDER BY — Newton, Jan 13 '16 at 10:34
**You can use the alias in the ORDER BY clause.** Not the group by clause unless using a cross apply (see answer by Evaldas Buinauskas) or you can use a "derived table" (see answer by Jiri Tousek) . There is no performance penalty (at all) when re-using the case expression as I have shown. — Paul Maxwell, Jan 13 '16 at 10:37

score 0 · Answer 2 · answered Jan 13 '16 at 09:30

Wrapping the query with computed column into a subquery may help you use that column:

SELECT remarks, COUNT(country) as no_of_countries
FROM (
  SELECT
    CASE 
      WHEN density_per_sq_km > 1000 THEN 'Overpopulated'
      WHEN density_per_sq_km > 500 THEN 'above average'
      WHEN density_per_sq_km > 250 THEN 'average'
      WHEN density_per_sq_km > 50 THEN 'below average' 
      ELSE 'Underpopulated'
    END as remarks,
    country
  FROM countries_by_population
) DT
GROUP BY remarks;

Evaldas Buinauskas · Answer 3 · 2016-01-13T10:44:25.913

0

You could use CROSS APPLY to assign alias to your column.

SELECT T.Remarks, COUNT(*) AS no_of_countries
FROM countries_by_population AS CBP
CROSS APPLY (
    SELECT CASE
            WHEN density_per_sq_km > 1000 THEN 'Overpopulated'
            WHEN density_per_sq_km > 500  THEN 'Above average'
            WHEN density_per_sq_km > 250  THEN 'Average'
            WHEN density_per_sq_km > 50   THEN 'Below average'
            ELSE 'Underpopulated'
        END
    ) AS T(Remarks)
GROUP BY T.Remarks;

This will create a column Remarks based on your density, which can be later used in GROUP BY.

Tip: APPLY and Reuse of Column Aliases article explains how to use CROSS APPLY to create reusable column aliases in detail.

edited Jan 13 '16 at 10:44

answered Jan 13 '16 at 09:38

Evaldas Buinauskas

13,739
11
55
107

This worked but i am not yet familiar with CROSS APPLY and the use of T in that query. I am a beginner and learning SQL – Newton Jan 13 '16 at 10:35
1

@Newton You could read this question: http://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql there's also a link which explains how you can use it to create aliases, that can be reused. It should be a good starting point. – Evaldas Buinauskas Jan 13 '16 at 10:39

SQL Server : how to use count

3 Answers3