how to count number of value occurrence in column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to count number of value occurrence in column

Author

Topic

darbar
Starting Member

16 Posts

Posted - 2013-10-24 : 09:38:58

HI,

I am having following data:
c1 c2 c3
122 40 1
122 107 2
122 83 3
122 79 3
122 81 3
122 31 4
122 206 5
122 207 5
122 208 5
122 147 6
122 148 6
122 131 6
122 36 7
122 153 8
122 151 8
122 86 9
122 82 10
122 112 11
122 37 12
122 38 12
122 74 13
122 154 14
122 155 14
122 213 15

I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.
Output
c1 p1 p2 p3
122 9 3 3
Support is highly appreciable.

Thanks in advance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-24 : 10:17:49

quote:
Originally posted by darbar

HI,

I am having following data:
c1 c2 c3
122 40 1
122 107 2
122 83 3
122 79 3
122 81 3
122 31 4
122 206 5
122 207 5
122 208 5
122 147 6
122 148 6
122 131 6
122 36 7
122 153 8
122 151 8
122 86 9
122 82 10
122 112 11
122 37 12
122 38 12
122 74 13
122 154 14
122 155 14
122 213 15

I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.
Output
c1 p1 p2 p3
122 9 3 3
Support is highly appreciable.

Thanks in advance.

I don't see 1 occurring 9 times in column 3, so this may not be what you want - but based on my understanding:

SELECT * FROM (
SELECT
	c1,
	c3,
	COUNT(*) N
FROM
	Tbl
GROUP BY
	c1,c3 ) s
PIVOT (MAX(N) FOR c3 IN ([1],[2],[3]))P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 10:56:59

And this as per my understanding


;With Numbers
AS
(
SELECT 0 AS N
UNION ALL
SELECT N +1
FROM Numbers
WHERE N + 1 <=9
)

SELECT *
FROM
(
SELECT c1,N
FROM (SELECT DISTINCT c1 FROM table)t
CROSS JOIN Numbers n
)p
OUTER APPLY (SELECT COUNT(*) AS Cnt
             FROM Table
             WHERE c3 LIKE '%' + CAST(p.N AS varchar(10)) + '%'
             )q
PIVOT(SUM(Cnt) FOR N IN ([1],[2],[3],...[9],[0]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

darbar
Starting Member

16 Posts

Posted - 2013-10-24 : 11:31:52

Thanks dear for kind support but this what I am not looking for let me clear more:
In C3 column: 1,2,4,7,9,10,11,13,15 is having single value so output would be 9. And 8,12,14 is having 2 value each so output would be 3. And 3,5,6 is having 3 value each so output would be 3 and so on. c1 column is n. Every C1 column value starts from 1 to n. and these value may be more than once, twice, and so on..
Please suggest me the solution.
Thanks for kind support.
Pradeep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-24 : 11:42:54

[code]
SELECT *
FROM
(
SELECT c1,c2,COUNT(*) AS Cnt
FROM table
GROUP BY c1,c2
)t
PIVOT (COUNT(c2) FOR Cnt IN ([1],[2],[3]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

darbar
Starting Member

16 Posts

Posted - 2013-10-24 : 21:32:58

query:
SELECT *
FROM (SELECT const_cd, building_booth_cd, COUNT(*) AS Cnt
FROM pollingstation
GROUP BY const_cd, building_booth_cd) t PIVOT (COUNT(building_booth_cd) FOR Cnt IN ([1], [2], [3], [4], [5], [6])) p
output:
c1 1 2 3 4 5 6 --Heading
122 103 37 9 1 1 0
123 143 26 8 0 0 0
124 183 30 0 0 0 0
125 101 37 5 2 1 0
126 95 43 13 2 1 0
127 23 23 17 9 4 1
128 49 26 11 2 5 0
129 30 32 7 7 7 0
130 116 35 14 4 0 0
131 97 62 3 2 0 0
My query:
select const_cd, sum(cnt)/6 from (select const_cd, count(building_booth_cd) as cnt from pollingstation group by const_cd, building_booth_cd having count(building_booth_cd)=6 ) t group by const_cd, cnt order by const_cd
my output is:
c1 6
127 1
I want output of query as follows:
c1 6 --Heading
122 0
123 0
124 0
125 0
126 0
127 1
128 0
129 0
130 0
131 0
Please suggest me the solution.
Thanks for kind cooperation.
Pradeep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 01:54:51

thats same as what you're getting from my query isnt it? if you just want 6 heading alone just add that in select list


DECLARE @YourValue int
SET @YourVlue = 6 --set any value here

SELECT *
FROM (SELECT const_cd, building_booth_cd, COUNT(*) AS Cnt
FROM pollingstation
GROUP BY const_cd, building_booth_cd
HAVING COUNT(*) = @YourValue) t PIVOT (COUNT(building_booth_cd) FOR Cnt IN ([1], [2], [3], [4], [5], [6])) p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources