Re: select & count

[Ryan Yagatich]

Jerry,
        You could also have 3 rows returned by running the following:
select count(*)
from account
where status in ('received','send','canceled')
group by status
;
This will return 3 rows, similar to the following
+-------------+-----------+
|  status     |  count(*) |
+-------------------------+
|  received   |     8     |
|  send       |    10     |
|  canceled   |    12     |
+-------------------------+
	I probably wouldn't use 'like' in this case, since the field is an 
enum. If you are interested in having the database only compare the 
first character of the string, maybe have 'status' be an enum of 
'r','s', 'c'. Furthermore, to make it even faster, try setting the 
status field to be an unsigned int, so you can do a status = 1, 2, 3 or 
whatever.

Thanks,
Ryan Yagatich
 ,__________________________________________________,
 / Ryan Yagatich                 Pantek Incorporated |
 \  http://www.pantek.com/linux   [EMAIL PROTECTED] |
 /  One Hour Emergency Response       (877) 546-8934 |
 \___________________________________________________|

Ian Sales wrote:
Jerry Swanson wrote:
| Field            | Type                                             
              | Null | Key | Default | Extra          |
+------------------+------------------------------------------------------------------+------+-----+---------+----------------+ 

| id               | int(10)                                          
              |      | PRI | NULL    | auto_increment |
| status           | enum('received','send','cancelled')              
              | YES  |     | NULL    |                |
| notes            | longblob                                         
              | YES  |     | NULL    |                |
| date             | datetime                                         
              | YES  |     | NULL    |                |
+------------------+------------------------------------------------------------------+------+-----+---------+----------------+ 


I have table account (see below). I need to get count of received, 
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where status='cancelled';

Is it possible to get count of each status in one query instead of 3?
 

- you could use a cross-tab query:
SELECT
date,
SUM(IF(CONCAT(status,id) LIKE 'received%',1,0)) as received,
SUM(IF(CONCAT(status,id) LIKE 'send%',1,0)) as send,
SUM(IF(CONCAT(status,id) LIKE 'cancelled%',1,0)) as cancelled
FROM
account
GROUP BY date;
- ian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]