Count the number of occurrence of 7 and 0 from the column

Question

I have a data like below format, now I want to count the occurrences of '7' and '0', like how many time it's showed up in the table from 'vi' column ??

thanks in advance

Seems like a straight forward `WHERE ... GROUP BY` query. Have you tried anything? — Martin Smith, Mar 20 '19 at 13:14
Possible duplicate of [SQL SELECT WHERE field contains words](https://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words) — Steve-o169, Mar 20 '19 at 13:15

score 2 · Accepted Answer · answered Mar 20 '19 at 13:18

Try this

  ;WITH CTE(ci ,vi )
AS
(
SELECT 1  ,7   UNION ALL
SELECT 2  ,0   UNION ALL
SELECT 3  ,0   UNION ALL
SELECT 4  ,7   UNION ALL
SELECT 5  ,7   UNION ALL
SELECT 6  ,10  UNION ALL
SELECT 7  ,15  UNION ALL
SELECT 8  ,0   UNION ALL
SELECT 9  ,16  UNION ALL
SELECT 10 ,7   UNION ALL
SELECT 11 ,0   UNION ALL
SELECT 12 ,35 
)
SELECT vi,COUNT(vi) Occurence
FROM CTE
WHERE vi IN (7,0) -- Specific values count Required use this where condition else remove
GROUP BY vi

Result

vi  Occurence
--------------
0      4
7      4

Idris · Answer 2 · 2019-03-20T13:29:53.593

2

MS SQL Can be achieved by GROUP BY

select vi, count(1) [COUNT] from @table1
group by vi
having vi in (7,0)

DEMO

edited Mar 20 '19 at 13:29

answered Mar 20 '19 at 13:19

Idris

351
1
9

score 1 · Answer 3 · answered Mar 20 '19 at 13:18

1

I think you can use sum and case-when like:

select
     sum(case when vi = 7 then 1 else 0 end) as [7Count],
     sum(case when vi = 0 then 1 else 0 end) as [0Count]
from table

answered Mar 20 '19 at 13:18

Eray Balkanli

7,752
11
48
82

Count the number of occurrence of 7 and 0 from the column

3 Answers3