Geek asks and answers

Conditional counter with multiple columns

I am trying to count individual records in 4 separate columns per row and then summarize the results.

For example, the table headers look something like this:

ID       Col1    Col2    Col3    Col4

Each column (save ID) can have a text value of W, X, Y, or Z. Columns can have the same value.

What I'm trying to do is to specify a method for counting each record in columns, but only the number W, X, Y and Z once per row. So if:

ID       Col1    Col2    Col3    Col4
          X        X       Y
          Y        W       X
          Z        Y       Y

Results table:

    Value    Count
      W        1
      X        2
      Y        3
      Z        1

Any help would be greatly appreciated.

+3
source share
2 answers

Maybe something is missing for me, but will it be so simple:

Select Val, Count(*)
From    (
        Select Id, Col1 As Val From Table1
        Union Select Id, Col2 From Table1
        Union Select Id, Col3 From Table1
        Union Select Id, Col4 From Table1
        ) As Z
Where Z.Val Is Not Null
Group BY Z.Val

Distinct Union , Union . - (Id).

SQL Fiddle ( SQL Server, MS Access)

+3

- 4 :

select 'W' as [Value], count(*) as Count from Table1
    where Col1='W' or Col2='W' or Col3='W' or Col4='W' union
select 'X' as [Value], count(*) as Count from Table1
    where Col1='X' or Col2='X' or Col3='X' or Col4='X' union
select 'Y' as [Value], count(*) as Count from Table1
    where Col1='Y' or Col2='Y' or Col3='Y' or Col4='Y' union
select 'Z' as [Value], count(*) as Count from Table1
    where Col1='Z' or Col2='Z' or Col3='Z' or Col4='Z'

: http://www.sqlfiddle.com/#!3/68aa3/11

: , , :

select V.Value as [Value],
  count(IIF(col1=[Value] or col2=[Value]
            or col3=[Value] or col4=[Value],1,NULL)) as [Count]
from Table1, (
  select distinct col1 as [Value] from table1 union
  select distinct col2 from table1 union
  select distinct col3 from table1 union
  select distinct col4 from table1) V
where V.value is not null
group by V.value
+3

More articles:


All Articles