SQL Server Count the number of multiple values in a single column in SQL

xmq68pz9 于 2023-10-15 发布在其他

关注(0)|答案(3)|浏览(109)

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?

sql-server

来源：https://stackoverflow.com/questions/71082321/count-the-number-of-multiple-values-in-a-single-column-in-sql

3条答案

按热度按时间

9jyewag01#

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 .

赞(0）回复(0）举报 2023-10-15

zyfwsgd62#

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

赞(0）回复(0）举报 2023-10-15

2ekbmq323#

If pivot output is acceptable you might use:

select
  count(case when code_id = 'ax1' then 1 end) [ax1],
  count(case when code_id = 'c39' then 1 end) [c39],
  count(case when code_id = 'x1a' then 1 end) [x1a]
 from mydb

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Count the number of multiple values in a single column in SQL

3条答案

相关问题

热门标签

最新问答