CODEWITHSUNDEEP
sqlsql-servercountcase
Newton
Newton

Reputation: 428

SQL Server : how to use count

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;

Upvotes: 1

Views: 131

Answers (3)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

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.

Upvotes: 0

Jiri Tousek
Jiri Tousek

Reputation: 12440

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;

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35563

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
;

Upvotes: 2

Related Questions