CODEWITHSUNDEEP
mysqlcountrows
Mokkun
Mokkun

Reputation: 726

Count multiple rows

I'd like to know if it is possible to count rows from the same table like so:

I have a table named tickets that contains the title, the content etc etc...

There is a column named status where the status can be: 

new 
waiting 
assign
closed 
solved

I'd like to be able to count

new tickets 
waiting tickets 
assign tickets 
closed tickets 
solved tickets

and all tickets in the same request, it that possible or should I make a new request for each one? Thanks!

Upvotes: 2

Views: 130

Answers (4)

spencer7593
spencer7593

Reputation: 108400

Do it in one request, because that is (almost always) more efficient than running multiple queries.

One way to get the result as a single row is to something like this:

SELECT SUM(status='new'     ) AS count_new
     , SUM(status='waiting' ) AS count_waiting
     , SUM(status='assign'  ) AS count_assign
  FROM tickets

If you also need a count of all tickets, you can add this to the SELECT list:

     , SUM(1)                 AS count_all

If you also want to add up both new and waiting together, you can also add this:

     , SUM(status IN ('new','waiting')) AS count_new_or_waiting

NOTE

 SUM(a=b)

is basically shorthand for: 

 SUM(IF(a=b,1,0))

There's a slight difference, in that the former will return a NULL when either a or b is null, so there's a potential to return a NULL, and there's a theoretical possibility the SUM could be NULL. One easy way to convert any NULL to 0 is to use an IFNULL function:

IFNULL(SUM(a=b))

Another way of avoiding NULL would be use the MySQL "null safe equality" comparator, 

SUM(a<=>b)

If you want something portable, then your best bet would be soemthing SQL-92 compliant:

SUM(CASE WHEN a=b THEN 1 ELSE 0 END)

The general approach is the same. We're looking at every row that satisfies the predicates (e.g. we might have a date range or rows, or a particular store, or something like that.) And we are checking each row to see if it should be counted in a particular bucket. And a row can be counted in multiple buckets (as demonstrated above).

One of the advantages of this approach (IMO) is that the query will return a zero count for a status that doesn't have any rows. Other approaches omit the row, and I think that makes the code that processes the rows a bit harder to understand. Processing the resultset from this query makes for more straightforward code.

The examples above use MySQL-specific syntax. The (more portable) ANSI equivalent is not as terse. Some might argue that ANSI syntax is more understandable, especially for those that are more familiar with DBMS other than MySQL.

 SELECT SUM(CASE WHEN status = 'new'     THEN 1 ELSE 0 END) AS count_new
      , SUM(CASE WHEN status = 'waiting' THEN 1 ELSE 0 END) AS count_waiting
   FROM tickets

This pattern can be extended to these counts by some grouping (for a department, a customer, a customer type.) We can either add a predicate (WHERE clause) to get a subset of rows, or we can add an expression expr in the SELECT list, and add a GROUP BY expr.

For example, get counts of tickets, for each distinct value "priority" e.g. CRITICAL, URGENT, IMPORTANT, LOW

 SELECT t.priority
      , SUM(CASE WHEN t.status = 'new'     THEN 1 ELSE 0 END) AS count_new
      , SUM(CASE WHEN t.status = 'waiting' THEN 1 ELSE 0 END) AS count_waiting
   FROM tickets t
  GROUP BY t.priority 
  ORDER BY IF(t.status='urgent',1,2), t.status

(That first expression in the order by clause will sort "urgent" first.)

With this approach, the "loop processing" isn't over the different counts, but rather, is now over the grouping column. This result set makes for convenient code, if the web page is going to display something like this:

 Priority     New    Waiting   Assign  Total
 URGENT       1       0        0       1
 CRITICAL     0       0        1       1
 IMPORTANT    12      7        3       22
 LOW          77      1048     2       1127

Upvotes: 2

Roland Jansen
Roland Jansen

Reputation: 2783

Use GROUP BY WITH ROLLUP to get the total count:

SELECT status, COUNT(status) AS count_status
FROM tickets 
GROUP BY status WITH ROLLUP;

Upvotes: 0

Ganesh Rengarajan
Ganesh Rengarajan

Reputation: 2006

Try this one.....

 SELECT status AS Word, 
 COUNT(status) AS Frequency FROM tickets
 GROUP BY status;

Upvotes: 1

user359040
user359040

Reputation:

Yes, this is what the group by functionality is for:

 select status, count(*) status_count
 from tickets
 group by status

Upvotes: 1

Related Questions