Count the number of times a variable occurs

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

SQL Server 2008 Forums

Transact-SQL (2008)

Count the number of times a variable occurs

Author

Topic

Jthrock
Starting Member

1 Post

Posted - 2012-04-06 : 11:46:59

Hello,

I am trying to find a way to count the total times the letter 'A' occurs and also the number of times the letter 'O' occurs in a given column.

So far I have tried using a case statement with count but I always end up with the number 1 because I do not know how to create a sum of the instances. This is what I have so far:

count(unique(case
when sku.status_code = 'A' then
1
end)) as Actives

Does anyone know a way to sum up the instances instead of return the value of one?

Thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-06 : 12:13:51

Are you using Microsoft SQL Server? UNIQUE should/would cause an error. You could use the LEN function like this:


LEN(sku.status_code)-LEN(REPLACE(sku.status_code,'A',''))

If you want to find the total number of occurrences, you would, of course, sum it up as in:

SELECT
   SUM(LEN(sku.status_code)-LEN(REPLACE(sku.status_code,'A','')))
FROM
   YourTable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 15:17:15

you want separate counts or cumulative count value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2012-04-06 : 18:33:19

It sounds to me like you have a single 1 character column, that has possible values of A, O, and other characters, and you simply want teh count of rows where the values in that column is A and the count where the value is O.

Correct?

If so you simply need to do:


SELECT COUNT(*), status_code FROM SomeTable WHERE status_code IN ('A', 'O') GROUP BY status_code

That's it.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-09 : 07:29:59

It would be better if you could post you DDL with some sample data.
Also tell us if you want count for A and Count for O separately.
Or, do you want the total count when any of these(ie: A or O) occurs in columns.

Vinu Vijayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources