Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h1>Solution 1: Two queries</h1> <p>You should use a table that contains your desired (correct) answer types:</p> <pre><code>| id | answer | --------------- | 1 | a1 | | 2 | a2 | etc. </code></pre> <p>Then you can count the results that actually exist in your table: </p> <pre><code>SELECT atype, COUNT( * ) cnt FROM answers JOIN mytable ON mytable.atype=answers.answer GROUP BY answers.answer; </code></pre> <p><em>(Replace <code>mytable</code> with the appropriate table name).</em></p> <p>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:</p> <pre><code>SELECT answer, '0' AS cnt FROM answers WHERE answer NOT IN( SELECT DISTINCT answer FROM answers JOIN mytable WHERE answer=mytable.atype ); </code></pre> <p><a href="http://sqlfiddle.com/#!2/ac481/3" rel="nofollow">Here is an example.</a></p> <h1>Solution 2: A counter table</h1> <p>Another way would be to use a counter table:</p> <pre><code>| id | answer | cnt | --------------------- | 1 | a1 | 0 | | 2 | a2 | 0 | etc. </code></pre> <p>Then every time you want to count the results, do:</p> <pre><code>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); </code></pre> <p>This will update the counter values in your answers table, and you can just <code>SELECT * FROM answers ORDER BY answer</code> to get your result.</p> <p>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. </p> <p><a href="http://sqlfiddle.com/#!2/374e9/3" rel="nofollow">Here is this example</a> (UPDATE statements are on the left side!)</p> <h1>Solution 3: Update upon write</h1> <p>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 <strong>update the counter values at the time users are answering the questions</strong>, instead of re-calculating all the entries everytime you want to know the count.</p> <p>This can easily be done. Everytime a question is answered correctly, increase the counter in the <code>answers</code> table:</p> <pre><code>UPDATE answers SET cnt=cnt+1 WHERE answers.answer='a1'; </code></pre> <p>And again, your query will be </p> <pre><code>SELECT * FROM answers ORDER BY answer; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow can I count existing and non-existing values with MySQL?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USweltraumpirat
    1. USweltraumpirat
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for excellent effort and please add a demo link as well. So someone would be convinced to look in your solution in bit detail as it is sufficiently detailed :)
      singulars
      1. PO
      1. USSami
    2. COI added examples for solutions #1 and #2. #3 is self-explanatory, I believe.
      singulars
      1. PO
      1. USweltraumpirat
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload