CODEWITHSUNDEEP
mysqldatabase
shtef
shtef

Reputation: 75

counting answers from only male users

I have database like this:

+-----+---------+--------------+
| id  | user_id | form_answers |
+-----+---------+--------------+
|  1  |    1    | yes          |
|  2  |    1    | M            |
|  3  |    2    | yes          |
|  4  |    2    | M            |
|  5  |    3    | no           |
|  6  |    3    | F            |
|  7  |    4    | no           |
|  8  |    4    | M            |
+-----+---------+--------------+

There was a form that inserted to this database . It had one question and dropdown list (male of female). Is it possible to count how many "yes" answers the male (M) users gave? It doesn't have to be counted...it can just select of all rows with answer "yes" from male users.

Upvotes: 0

Views: 93

Answers (2)

John Woo
John Woo

Reputation: 263803

SELECT  COUNT(*) totalUsers
FROM
    (
        SELECT  user_id
        FROM    tableName
        WHERE   form_answers IN ('YES','M')
        GROUP   BY user_ID
        HAVING  COUNT(*) = 2
    ) s

Upvotes: 1

fthiella
fthiella

Reputation: 49079

You can join answers tabe with itself. This will return the list of "yes" answers male users gave:

SELECT a1.*
FROM answers a1 inner join answers a2
     on a1.user_id=a2.user_id
        and a1.form_answers='yes'
        and a2.form_answers='M'

to count the rows, just substitute the select line with this:

SELECT COUNT(*)

see fiddle here.

Upvotes: 2

Related Questions