List of Questions that I have asked by this blog’s readers

1. Rating: +2

   

   QUESTION
   Hi, I have a table my_table in my database as follows: mysql> desc my_table;
   +—————-+————-+——+—–+———+—————-+
   | Field | Type | Null | Key | Default | Extra |
   +—————-+————-+——+—–+———+—————-+
   | id | int(11) | NO | PRI | NULL | auto_increment |
   | a_no | varchar(20) | YES | MUL | NULL | |
   | status | int(1) | YES | MUL | NULL | |
   …
   +—————-+————-+——+—–+———+—————-+
   13 rows in set (0.00 sec)
   Note: a_no may be repetitive in that table (it is not unique).
   My question is:
   How can I extract for each a_no, two more fields:
   1. total number of a_no
   2. total number of success (success marked with status = 0)
   And only show the rows which the number of success is greater than zero.
   ur fast reply is very very much appreciated ?

   ANSWER
   Hi,
   1. You should use group by clause
   2. Use SUM and IF functions in order to count the success cases.
   3. use HAVING clause to show only the rows which the number of success is greater then 0.
   Your query should look like:
   select a_no, count(*) as c, sum(IF(status=0,1,0)) as t from my_table where … group by a_no having t>0 order by c desc limit 10;