"cruiserweight" wrote in message
> 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.
FAQ
Search
Profile
Private Messages
Log in
