Count the number of multiple values in a single column in SQL-CodePudding

I have a long list of string values that I would like to count, in one particular column of a table. I know this works for counting all unique values.

SELECT
    code_id ,
    COUNT(*) AS num
FROM
    mydb
GROUP BY
    code_id
ORDER BY
    code_id

I only have a certain selection of values to count, therefore do now want all. My list is long, but for example, if I just wanted to count the numbers of strings 'ax1', 'c39', and 'x1a' in my code_id column? I've seen examples with multiple lines of code, one for each value which will be huge for counting many values. I'm hoping for something like :

SELECT
    code_id ,
    COUNT(* = ('ax1, 'c39', 'x1a')) AS num
FROM
    mydb
GROUP BY
    code_id
ORDER BY
    code_id

Desired output would be

code_id    count
ax1         39
c39         42
x1a          0

Is there an easy way, rather than a line of code for each value to be counted?

CodePudding user response：

Create a CTE that returns all the string values and a LEFT join to your table to aggregate:

WITH cte AS (SELECT code_id FROM (VALUES ('ax1'), ('c39'), ('x1a')) c(code_id))
SELECT c.code_id, 
       COUNT(t.code_id) AS num
FROM cte c LEFT JOIN tablename t
ON t.code_id = c.code_id
GROUP BY c.code_id;

See the demo.

CodePudding user response：

I think this should work.

SELECT
    code_id ,
    sum(1) AS num
FROM Mydb
WHERE code_id in ('ax1', 'c39', 'x1a')
GROUP BY code_id
ORDER BY code_id