Re: query question

cruiserweight wrote:
> 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.

Hi,

This is a theory newsgroup, so we don't usually discuss that sort of thing. You'd be better off asking mysql questions in a mysql forum.

But since you're here, I'll tell you
1) It's quite easy to do it in two queries:

   select count(*) from Table where opened != '0000-00-00';    select count(*) from Table where closed != '0000-00-00';

2) It's possible to do with one query, but it's more than twice as complex as the individual queries:

   select

     sum(case opened
       when '0000-00-00' then 0
       else 1
     end) as opened,
     sum(case closed
       when '0000-00-00' then 0
       else 1
     end) as closed

3) No, you don't need to make a separate table

You might need to tweak the above SQL; I just typed it in and didn't test-execute it. Maybe some tweaking to do to get the date literals correct.

HTH Marshall

PS. MySQL has extensive online documentation.

http://dev.mysql.com/doc/mysql/en/index.html Received on Sun Oct 09 2005 - 19:06:24 CEST