Count as single with multiple rows in mysql

Question

I have the following table and would like to get the result as count

Users   QuestionType    Question    Answer
User 1  Employment      Question 1      Yes
                        Question2       NULL

User 1  Job History     Question 3      Yes
                        Question 4      Yes
                        Question 5      No

User 1  Work Status     Question 6      null
                        Question 7      null

User 2  Job History     Question 3      Yes
                        Question 4      Yes
                        Question 5      No

User 2  Work Status     Question 6      yes
                        Question 7      yes

Result

            Count of Users
Employment      1
Job History     2
Work Status     1

How do I get a single count for multiple rows from within mysql?

I don't understand your results. Why would the count for Pet be 1 and not 2, or Wild be 3? If that's what you want, just use COUNT(Animal) as Count GROUP BY TYPE in your query. — daShier
– daShier, Commented May 4, 2020 at 16:33
and where does Others come from ? Haven't you tried anything to get those results ? — Barbaros Özhan
– Barbaros Özhan, Commented May 4, 2020 at 16:36
He seems to want a count of 1 even if it's more than 1 ("count as single with multiple rows"), but then what's the point of even having count? It's just a constant 1. — Aioros
– Aioros, Commented May 4, 2020 at 16:36
I simplified the example. we have multiple questions that needs to be asked in questionnaire. If the user selects any one value then we need to count it as 1. For e.g. If the user answers to Question1 or Question2 then we need to count 1 for an aggregate report. If the user did not answer any of the Question - then it is 0. — Shankar
– Shankar, Commented May 4, 2020 at 16:37

Hogan · Accepted Answer · 2020-05-04 17:47:09Z

1

This will give us a list of question types

SELECT DISTINCT QUESTION_TYPE 
FROM TABLE_NAME

This will give us a count of the number of questions of a given type

SELECT QUESTION_TYPE, COUNT(*) AS COUNT
FROM TABLE_NAME
WHERE ANSWER IS NOT NULL
GROUP BY QUESTION_TYPE

Now we can join them together.

SELECT BASE.QUESTION_TYPE, COALESCE(CNT.COUNT,0) AS COUNT
FROM (
  SELECT DISTINCT QUESTION_TYPE 
  FROM TABLE_NAME
) AS BASE
LEFT JOIN (
  SELECT QUESTION_TYPE, COUNT(*) AS COUNT
  FROM TABLE_NAME
  WHERE ANSWER IS NOT NULL
  GROUP BY QUESTION_TYPE
) CNT ON BASE.QUESTION_TYPE = CNT.QUESTION_TYPE

That give a count of answered questions, if you want a "count of users" then you have to change count to look like this:

SELECT QUESTION_TYPE, COUNT(USERS) AS COUNT
FROM TABLE_NAME
WHERE ANSWER IS NOT NULL
GROUP BY QUESTION_TYPE

This answer is more complicated then a simple group by because there is a potential for there to be question types that no one has answered. If that never happens then use Gordan's answer.

edited May 4, 2020 at 17:47

answered May 4, 2020 at 17:00

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

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

1 Comment

Shankar Over a year ago

Thank you - I did create another table to use it as reference and was able to do a subquery with join and use the entire query as another subquery for the counts

Gordon Linoff · Accepted Answer · 2020-05-04 17:21:43Z

0

If you want a count of users on non-NULL answers:

select questiontype, count(distinct user_id)
from t
where answer is not null
group by questiontype;

answered May 4, 2020 at 17:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Hogan Over a year ago

this won't get zero for question types with all nulls

Collectives™ on Stack Overflow

Count as single with multiple rows in mysql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related