This is what I was looking for. Why the query is call cross-tab?
TH
On Thu, 13 Jan 2005 02:09:45 +0000, Ian Sales <[EMAIL PROTECTED]> 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
>
> --
> +-------------------------------------------------------------------+
> | 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]
