CODEWITHSUNDEEP
mysqlgroup-bycountcase
TWLATL
TWLATL

Reputation: 2879

Using a MySQL case condition to create a column in a query that uses count and group by

I have a data schema that looks like this:

"id"|"utest_id"|"member_id"|"utest_step_id"|"value"
699 |21        |162        |522            |4
704 |21        |163        |522            |2
709 |21        |164        |522            |2
714 |21        |165        |522            |2
719 |21        |166        |522            |3
724 |21        |167        |522            |5
729 |21        |168        |522            |4
734 |21        |169        |522            |2
739 |21        |170        |522            |1
744 |21        |171        |522            |5
749 |21        |172        |522            |3
754 |21        |173        |522            |3
759 |21        |174        |522            |4
764 |21        |175        |522            |3
769 |21        |176        |522            |5
774 |21        |177        |522            |4
779 |21        |178        |522            |1
784 |21        |179        |522            |4
789 |21        |180        |522            |3
794 |21        |181        |522            |2
799 |21        |182        |522            |3
804 |21        |183        |522            |1

I have a MySQL Query that groups by value and returns the count of each value.

SELECT URPS.value, count(URPS.value) AS count,
(CASE WHEN URPS.member_id = '183' THEN 'true' END) as MemberAnswer
FROM utest_results_per_step URPS
WHERE URPS.utest_step_id = '522' 
GROUP BY URPS.value;

Which gives me the proper grouping and count. 

value | count | MemberAnswer
1       21      NULL
2       20      NULL
3       26      NULL
4       21      NULL
5       12      NULL

I have a CASE condition in the query that creates a column called MemberAnswer, and if the member_id value is true for the given comparison, I want it to have a 'true' entry in that column for that value's row. However, I'm only getting NULL.

Is this possible in a single query, and if so how would I restructure my query to return this?

Upvotes: 1

Views: 25

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You need to wrap the CASE expression in an aggregate function and use another CASE to check the result returned by the aggregate.

So instead of:

(CASE WHEN URPS.member_id = '183' THEN 'true' END) as MemberAnswer

you should use:

CASE 
   WHEN COUNT(CASE WHEN URPS.member_id = '183' THEN 1 END) > 0 THEN 'true'
   ELSE 'false'
END AS MemberAnswer

So your query will be:

SELECT URPS.value, 
       count(URPS.value) AS count,
       CASE 
          WHEN COUNT(CASE WHEN URPS.member_id = '183' THEN 1 END) > 0 THEN 'true'
          ELSE 'false'
       END AS MemberAnswer
FROM utest_results_per_step URPS
WHERE URPS.utest_step_id = '522' 
GROUP BY URPS.value;

Output:

value count MemberAnswer
------------------------
1     3     true
2     5     false
3     6     false
4     5     false
5     3     false

Demo here

Upvotes: 1

Related Questions