[thelist] count() multiple columns in a single query?

[Jeremy Weiss]

Matt, thanks for getting back with me.

Both of the queries threw errors though, shown below, that don't make a lick
of sense to me.

You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near 'and (closed is
null or closed='') and received_by = '' then 1 else 0) as first_c' at line 1

You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near ') as
first_count, sum(case when received_by!='' then 1 else 0) as second_count f'
at line 1

For what it's worth, this is on a MySQL 5 server.

-jeremy



On Dec 13, 2007 12:47 PM, Matt Warden <mwarden at gmail.com> wrote:

> On 12/13/07, Jeremy Weiss <eccentric.one at gmail.com> wrote:
> > Script 1 - looks for records with nothing in the closed column
> > Select COUNT(*) FROM followup WHERE fk_id=$id AND ((closed IS NULL) OR
> > (closed = ''))
> >
> > Script 2 - looks for records from the same records set as above for
> records
> > with nothing in the received column
> > Select COUNT(*) FROM followup WHERE fk_id=$id AND received_by != '' AND
> > ((closed IS NULL) OR (closed = ''))
>
> Try this:
>
> select
>     sum(case when fk_id=$id and (closed is null or closed='') and
> received_by='' then 1 else 0) as first_count,
>     sum(case when fk_id=$id and (closed is null or closed='') and
> received_by!='' then 1 else 0) as second_count
> from followup
>
> So, you can see I basically took your where clauses and made them case
> conditions. The following reformulation is equivalent, since much of
> the where clauses are the same:
>
> select
>     sum(case when received_by='' then 1 else 0) as first_count,
>     sum(case when received_by!='' then 1 else 0) as second_count
> from followup
> where  fk_id=$id and (closed is null or closed='')
>
>
> --
> Matt Warden
> Cincinnati, OH, USA
> http://mattwarden.com
>
>
> This email proudly and graciously contributes to entropy.
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>