CODEWITHSUNDEEP
sqlcountmultiple-columns
SQLn00b
SQLn00b

Reputation: 13

MS SQL: Count entries as one in multiple columns

I try to count how many times a value is in either column. If the value is in both colums on the same row, it should only be counted as one.

     |   a     |    b 
########################
1    | henrik  | hans
2    | anne    | henrik
3    | peter   | finn
4    | henrik  | henrik
5    | hans    | anne

The count for 'henrik' should be 3. I have tried to count and group, but without any succes.

Upvotes: 1

Views: 758

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this:

SELECT ab, COUNT(DISTINCT id) AS 'Count'
FROM
(
    SELECT id, a AS ab FROM table1
    UNION ALL
    SELECT id, b       FROM table1
) AS T
GROUP BY  ab;

SQL Fiddle Demo.

This will give you:

|     AB | COUNT |
------------------
|   anne |     2 |
|   finn |     1 |
|   hans |     2 |
| henrik |     3 |
|  peter |     1 |

Upvotes: 2

paul
paul

Reputation: 22001

select count(*)
from   table
where  a = 'henrik' or b = 'henrik'

Upvotes: 2

Related Questions