SQL count all where condition ist not Fullfilled

I have a problem for which I have no solution. I have a table with 30,000 entries and a column of zip codes.

I now wanted to calculate the total for each individual ZIP code

SELECT
zipcode, count(zipcode)
FROM
testtable
Group by zipcode

Then ill get a result like this :

Zip Codes	SUM
90010	5025
90020	2503
90030	4565
90040	2134
12346	1
161681	16
NULL	15105
96874	1123

However, now I only want to add the ZIP codes in a special range and all those that do not correspond to this range to "Unknown" in one line.

The result should then look something like this:

Zip Codes	SUM
90010	5025
90020	2503
90030	4565
90040	2134
unknown	18000

Ill really have no idea how to solve this ? Do i need a type of temporary table ?

Thanks for your help and Time :)

Solution

You can use a CROSS APPLY to map your original zipcode to a new value and then group and count that:

SELECT ISNULL(M.MappedZipcode, 'unknown') AS [Zip Codes], count(*) AS SUM
FROM testtable
CROSS APPLY (
    SELECT CASE
        WHEN zipcode BETWEEN '90000' AND '99999'
        THEN zipcode
        /* ELSE NULL is implied */
        END AS MappedZipcode
) M
GROUP BY M.MappedZipcode
ORDER BY [Zip Codes]

Depending on your source data, you may need to trim zip+4 values using the LEFT() function and may also need to cast the result to a string long enough to hold the "unknown" value.

Sample results:

Zip Codes	SUM
90010	1
90020	2
90030	3
90040	4
96874	1
unknown	6

See this db<>fiddle.

Side note: Best practice is to always store zip codes as text, not as integers.