Re: query question

"cruiserweight" <bayon86_at_yahoo.com> wrote in message news:1128836990.863752.213780_at_f14g2000cwb.googlegroups.com...
> I'm using MySQL. I have a table that looks like this:
>
> +-----+---------------------+---------------------+
> | cid | opened | closed |
> +-----+---------------------+---------------------+
> | 1 | 2005-10-09 10:12:30 | 2005-10-09 11:37:36 |
> | 2 | 2005-10-10 11:12:45 | 0000-00-00 00:00:00 |
> | 3 | 2005-10-10 12:33:15 | 0000-00-00 00:00:00 |
> | 4 | 2005-10-11 10:36:16 | 0000-00-00 00:00:00 |
> | 5 | 2005-10-12 11:55:22 | 0000-00-00 00:00:00 |
> +-----+---------------------+---------------------+
>
> What I want as a query result is the total number of rows in 'opened'
> and the total number of rows in closed that have a date assigned, i.e.
> opened=5, closed=1. I'm relatively new to MySQL, and I can't for the
> life of me get a handle on this one. Can this be done with a single
> query? Do I need to make 'closed' a separate table?
>
> Thanks very much in advance for any and all suggestions.
>

Try this:

select

    count (all opened) as opened,
    count (all closed) as closed
from your_table;

This should do what you want, provided that MYSQL accepts this construct, treats the "all" keyword the same way other systems do, and recognizes the dates that are all zeroes as "missing data", better known as NULL. Received on Fri Oct 14 2005 - 01:39:44 CEST