CODEWITHSUNDEEP
sqlsql-serverselectcount
foundcake
foundcake

Reputation: 13

Count value across multiple columns

I am looking to count the number of times set of values occurred in a table. These values could occur in up to 10 different columns. I need to increment the count regardless of which column it is in. I know how I could count if they were all in the same column but not spanning multiple columns.

Values can be added in any order. I have about a thousand 

Cpt1    Cpt2    Cpt3    Cpt4    Cpt5
63047   63048   63048   NULL    NULL

I would want to for this row I'd expect this as the result

63047 1 
63048 2

Upvotes: 1

Views: 174

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Many databases support lateral joins, of one type of another. These can be used to simplify this operation. Using the SQL Server/Oracle 12C syntax:

select v.cpt, count(*)
from t cross apply
     (values (cpt1), (cpt2), . . . 
     ) v(cpt)
where cpt is not null
group by v.cpt;

Upvotes: 0

cdonner
cdonner

Reputation: 37718

Something like this might work (after adapting to your value domain and data types):

create table t1 
(i1 int,
 i2 int,
 i3 int);

 insert into t1 values (1,0,0);
 insert into t1 values (1,1,1);
 insert into t1 values (1,0,0);

 declare @i int = 0;
 select @i = @i + i1 + i2 + i3 from t1;

 print @i;

 drop table t1;

Output is: 5

Upvotes: 0

deroby
deroby

Reputation: 6002

It's not entirely clear what your table exactly looks like, but I'm guessing that what you're looking for is:

SELECT row_count = COUNT(*),
       row_count_with_given_value = SUM ( CASE WHEN field1 = 'myValue' THEN 1 
                                               WHEN field2 = 'myValue' THEN 1
                                               WHEN field3 = 'myValue' THEN 1
                                               WHEN field4 = 'myValue' THEN 1 ELSE 0 END)
  FROM myTable

Assuming the fieldx columns are not NULL-able, you could write it like this too:

SELECT row_count = COUNT(*),
       row_count_with_given_value = SUM ( CASE WHEN 'myValue' IN (field1, field2, field3, field4) THEN 1 ELSE 0 END)
  FROM myTable

Upvotes: 0

Mureinik
Mureinik

Reputation: 312404

You could use a union all call to treat them as one column:

SELECT   col, COUNT(*)
FROM     (SELECT col1 FROM mytable
          UNION ALL
          SELECT col2 FROM mytable
          UNION ALL
          SELECT col3 FROM mytable
          -- etc...
         ) t
GROUP BY col

Upvotes: 1

Related Questions