How can I count existing and nonexistent values ​​with MySQL?

Question

I am new to MySQL.

I have a table with response IDs. The answers may look like this: a1, a2, a3 ... , but due to some problems, some of them are NULL , some of them are empty, and some others, such as 1 a , etc.

Now I want to calculate the number of identifiers using a1 a2 a3 . But how can this be done by leaving others, such as NULL , spaces and trash.

The result should look like this:

 atype count a1 45 a2 0 a3 56 

If there is no line entry for a specific response, the counter should be 0.

Answer 1

Solution 1: two queries

You should use a table that contains the necessary (correct) types of answers:

 | id | answer | --------------- | 1 | a1 | | 2 | a2 | etc. 

Then you can count the results that really exist in your table:

 SELECT atype, COUNT( * ) cnt FROM answers JOIN mytable ON mytable.atype=answers.answer GROUP BY answers.answer; 

(Replace mytable with the appropriate table name).

Of course, this will only return existing results. To count the null rows, you can look for answers that do not appear in your table:

 SELECT answer, '0' AS cnt FROM answers WHERE answer NOT IN( SELECT DISTINCT answer FROM answers JOIN mytable WHERE answer=mytable.atype ); 

Here is an example.

Solution 2: counter table

Another way is to use a counter table:

 | id | answer | cnt | --------------------- | 1 | a1 | 0 | | 2 | a2 | 0 | etc. 

Then each time you want to count the results, do:

 UPDATE answers SET cnt=0; UPDATE answers SET cnt= (SELECT cnt FROM ((SELECT answers.answer, COUNT(*) AS cnt FROM answers JOIN mytable ON answers.answer=myTable.aType GROUP BY answers.answer) AS tbl) WHERE answers.answer=tbl.answer) WHERE EXISTS (SELECT cnt FROM ((SELECT answers.answer, COUNT(*) AS cnt FROM answers JOIN mytable ON answers.answer=mytable.atype GROUP BY answers.answer) AS tbl) WHERE answers.answer=tbl.answer); 

This will update the counter values ​​in your answer table, and you can simply SELECT * FROM answers ORDER BY answer to get the result.

Be careful: I believe that the second version, although convenient, will take much more processing power than the first, because of all the necessary subqueries.

Here is an example (UPDATE statements are on the left side!)

Solution 3: Update after recording

The best and least productive hungry solution for such cases, in your opinion, is to create a counter table similar to the one that I described in # 2, but update the counter values ​​while users answer questions , instead of recounting all records every time you want to know the score.

It is easy to do. Each time a question is answered correctly, increase the counter in the answers table:

 UPDATE answers SET cnt=cnt+1 WHERE answers.answer='a1'; 

And again your request will be

 SELECT * FROM answers ORDER BY answer; 

Answer 2

 select a.atype,count(*) as `Count` from (select 'a1' as atype union all select 'a2' as atype union all select 'a3' as atype )a left join <your_table> b on a.atype =b.atype group by atype 

Answer 3

I tried to present all the main options, each request is followed by a demo link. The link contains a description

 select AType,count(*) as Count from tb2 where atype!='' and atype is not null and atype!='0' group by atype 

Link1 (Best One) To count the answers of each (existing) type other than balank 0 and null

 select (select count(id) from tb2 where atype='a1') as A1,(select count(id) from tb2 where atype='a2') as A2,(select count(id) from tb2 where atype='a3') as A3; 

Link2 (the simplest and most suitable for you) counting responses of types a1, a2 or a3 types (my older method of getting similar results for link3)

 select AType,count(*) as Count from tb2 where atype in('a1','a2','a3') group by atype 

Link3 only counts responses of types a1, a2 or a3.

The first and last links do not take into account those types that are completely missing from the data. For example, if there is no answer with type a2, then the first and third request will indicate only the number of answers with types a1 and a3 and will not mention the answers atype = a2. However, the second request does.