0

I am having hard time writing the query that counts occurrence of specific value in multiple columns. I cannot change the structure of db. I know I can do this using multiple queries and union at the end, but I was wondering is there any smarter solution.

Here is an example.    

+-----------+------------+------------+-------------+
| A            | B            | C            | D            |
+-----------+------------+------------+------------+
| value1   | value2    | value1    | value3    |
| value2   | value3    | value1    | value4    |
| value1   | value3    | value2    | value1    |
| value3   | value1    | value1    | value1    |
| value1   | value1    | value1    | value1    |
+-----------+------------+------------+------------+

I would like to count number of fields where value=value1 for each column. A result set should look like this:

   

+-----------+------------+
| Column  | Count     |
+-----------+------------+
| A            | 3            |
| B            | 2            |
| C            | 4            |
| D            | 3            |
+-----------+------------+

Thanks

Improve this question
1
  • I have tried with union, to make count for every column in separate query and then to create union. Commented Jun 12, 2015 at 11:34

2 Answers 2

Reset to default
2

If you want it on a single row similar to Anirudh's response try this:

SELECT SUM(A='Value1'),
    SUM(B='Value1'),
    SUM(C='Value1'),
    SUM(D='Value1')
FROM TableName

Alternatively to have it the way you specified you can use this:

SELECT 'A', SUM(A='Value1')
FROM TableName
UNION
SELECT 'B', SUM(B='Value1')
FROM TableName
UNION
SELECT 'C', SUM(C='Value1')
FROM TableName
UNION
SELECT 'D', SUM(D='Value1')
FROM TableName

Edit: SQL Fiddle http://sqlfiddle.com/#!9/5a99a/6

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This solves my problem. I was so focused on how to get one row for each column, that I didn't take this into consideration at all. I can handle this in code quite easy. It is very simple and elegant approach. Thanks. –
0

you can do it by using horizontal aggreagation

`SELECT sum(case when A=value1 then 1 else 0 END) as CountValue1,sum(case when A=value2 then 1 else 0 END) as CountValue2,sum(case when A=value3 then 1 else 0 END) as CountValue3   FROM tableName;`

by this you can convert rows into column but not column into rows

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.