CODEWITHSUNDEEP
mysqlsqlcount
jaya
jaya

Reputation: 327

How can I count existing and non-existing values with MySQL?

I am new to MySQL.

I have a table with answer ids. Answers can look like this:a1, a2, a3 ..., but due to some problems some are NULL, some are blank, and some are others like 1 a etc.

Now I want to calculate the count of ids with a1 a2 a3 distinctly. But how is it possible to do this leaving others like NULL, blanks and garbage.

The output should look like this

atype count 
a1      45
a2       0
a3      56

If there is no row entry for a particular answer, the count should be 0.

Upvotes: 4

Views: 1749

Answers (3)

Sami
Sami

Reputation: 8419

I have tried to present all major choices, each query is followed by demo link. Link contains 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 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 (Simple and most suitable for you) count answers of types a1,a2 or a3 only (my older method to get similar results to link3)

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

Link3 count answers of types a1,a2 or a3 only.

First and last link do not take into account those types which are totally not present in the data. For example, If there is no answer with type a2 then first and third query would tell only number of answers with type a1 and a3 and will not mention answers of atype=a2. However 2nd query does.

Upvotes: 0

weltraumpirat
weltraumpirat

Reputation: 22604

Solution 1: Two queries

You should use a table that contains your desired (correct) answer types:

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

Then you can count the results that actually 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 zero 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: A counter table

Another way would be to use a counter table:

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

Then every 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 answers table, and you can just SELECT * FROM answers ORDER BY answer to get your result.

Be warned, though: I believe the second version, while convenient, will take a lot more computing power than the first one, due to all the subqueries needed.

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

Solution 3: Update upon write

The best and least performance hungry solution for use cases like yours, in my opinion, is to create a counter table like the one I described in #2, but update the counter values at the time users are answering the questions, instead of re-calculating all the entries everytime you want to know the count.

This can easily be done. Everytime 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 query will be 

SELECT * FROM answers ORDER BY answer;

Upvotes: 3

Joe G Joseph
Joe G Joseph

Reputation: 24046

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

Upvotes: 0

Related Questions