Question: Table Name : user\_test\_details 

[![enter image description here](https://i.stack.imgur.com/pIir7.png)](https://i.stack.imgur.com/pIir7.png)

I want result as per below,

```
question_id   option_a   option_b   option_c   option_d   option_e
1             0          1          0          0          1
2             2          0          0          0          0
3             0          0          1          0          1
4             0          0          2          0          0

```

I want count of, how many times option\_a is repeating in question\_id 1, if not then 0, if yes then as per count. Same as other options (option\_b, option\_c, option\_d, option\_e). More declaration, Here question\_id 4, option\_c is repeating 2 times so, in result, question\_id 4, option\_c value is 2.

Answer: 
Use conditional aggregation:

```
SELECT
    question_id,
    SUM(CASE WHEN option_a = 'option_a' THEN 1 ELSE 0 END) AS option_a,
    SUM(CASE WHEN option_b = 'option_b' THEN 1 ELSE 0 END) AS option_b,
    SUM(CASE WHEN option_c = 'option_c' THEN 1 ELSE 0 END) AS option_c,
    SUM(CASE WHEN option_d = 'option_d' THEN 1 ELSE 0 END) AS option_d,
    SUM(CASE WHEN option_e = 'option_e' THEN 1 ELSE 0 END) AS option_e,
FROM user_test_details
GROUP BY question_id

```
Try something like this.

```
SELECT DISTINCT question_id, 
    COALESCE(optA.option_a,0) as option_a, 
    COALESCE(optB.option_b,0) as option_b 
FROM user_test_details
LEFT JOIN 
    (
    SELECT question_id, count(option_a) as option_a FROM user_test_details 
    WHERE
    option_a is not null or option_a <> '' group by option_a) 
AS optA
ON user_test_details.question_id = optA.question_id

LEFT JOIN 
    (
    SELECT question_id, count(option_b) as option_b FROM user_test_details 
    WHERE
    option_b is not null or option_b <> '' group by option_b) 
AS optB
ON user_test_details.question_id = optB.question_id

```

And yes I know it looks dirty.