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
-- +-------------------------------------------------------------------+ | Ian Sales Database Administrator | | | | eBuyer http://www.ebuyer.com | +-------------------------------------------------------------------+
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
