Count how many times contains a single value

Question

Post reply

Count how many times contains a single value

mrivero1961

SSC Enthusiast

Points: 164
More actions
April 11, 2013 at 1:13 pm

#394570

Hello everyone, I need your help.
This is my table:
+------+----+-----------------------+
| Att | q | x |
+------+----+-----------------------+
| Att | 9 | 5,4,4,4,5,3,5,4,5 |
| Lav | 3 | 5,5,3 |
| Pred | 11 | 4,5,5,5,5,5,5,4,5,5,4 |
| RGdM | 3 | 5,3,5 |
+------+----+-----------------------+
I need count how many times contains a single value, I mean:
The row *Att* contains 4 times the value 5 and 4 times the value 4 and 1 time the value 3, etc.
Can you help me?
Thank you in advance.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 1

Erin Ramsay

SSCrazy Eights

Points: 9728

April 11, 2013 at 1:19 pm

#1605757

Google UNPIVOT and then count aggregate.

Lowell SSC Guru Points: 323546 More actions · Answer 2

i think since the data is comma delimtied string, you can be sneaky and use LEN / LEN(REPLACE

/*

Att q x Numfives Numfours Numthrees

---- ---- --------------------- ----------- ----------- -----------

Att 9 5,4,4,4,5,3,5,4,5 4 4 1

Lav 3 5,5,3 2 0 1

Pred 11 4,5,5,5,5,5,5,4,5,5,4 8 3 0

RGdM 3 5,3,5 2 0 1

*/

with mmySampleData ( Att,q,x )

AS

(

SELECT 'Att','9','5,4,4,4,5,3,5,4,5' UNION ALL

SELECT 'Lav','3','5,5,3' UNION ALL

SELECT 'Pred','11','4,5,5,5,5,5,5,4,5,5,4' UNION ALL

SELECT 'RGdM',' 3','5,3,5'

)

select

mmySampleData.*,

LEN(x) - LEN(REPLACE(X,'5','')) As Numfives,

LEN(x) - LEN(REPLACE(X,'4','')) As Numfours,

LEN(x) - LEN(REPLACE(X,'3','')) As Numthrees

FROM mmySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Or if you don't want to have a hard coded set of value to look for and your list is delimited we can use the DelimitedSplit8K.

if OBJECT_ID('tempdb..#MyTable') is not null

drop table #MyTable

create table #MyTable

(

Att varchar(4),

x varchar(25)

)

insert #MyTable

select 'Att', '5,4,4,4,5,3,5,4,5' union all

select 'Lav', '5,5,3' union all

select 'Pred', '4,5,5,5,5,5,5,4,5,5,4' union all

select 'RGdM', '5,3,5'

select Att, Item as RepeatedValue, COUNT(*) as NumOccurences

from #MyTable

cross apply dbo.DelimitedSplit8K(x, ',')

group by Att, Item

order by Att, Item

You can find the code to create the DelimitedSplit8K function by following the link in my signature about splitting strings.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/