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?
3条答案
按热度按时间9jyewag01#
Create a
CTE
that returns all the string values and aLEFT
join to your table to aggregate:See the demo .
zyfwsgd62#
I think this should work.
2ekbmq323#
If pivot output is acceptable you might use: