I think what you need is a pivot table (aka: cross tab report):
SELECT c.id
, c.campaign_name
, count(1) as total
, SUM(if(a.status='optin',1,0)) as optin
, SUM(if(a.status='optout',1,0)) as optout
FROM addresses as a
INNER JOIN addresses_incampaign as i
on a.email_address = i.email_address
INNER JOIN campaigns as c
on i.campaign_id = c.id
WHERE a.user_id = "1"
AND i.user_id = "1"
GROUP BY c.id, c.campaign_name
ORDER BY c.id
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Haneda <[EMAIL PROTECTED]> wrote on 08/06/2004 01:19:21 AM:
> Mysql 4
>
> SELECT c.campaign_name, count(*), c.id FROM addresses as a INNER JOIN
> addresses_incampaign as i on (a.email_address = i.email_address) INNER
JOIN
> campaigns as c on (i.campaign_id = c.id) WHERE a.user_id = "1" AND
i.user_id
> = "1" GROUP BY c.id ORDER BY c.id
>
> This gives me
> FooNmae 100
> BarName 250
> FUD name 127
>
> Within the addresses table is a field called status, it is a enum and
can be
> 'optin' or 'optout'. I need to also show how many there are of each of
> those as well. I have looked at count(a.status) but that just returns
the
> total numbers listed above, count(DISTINCT a.status='optin') gives me 2
for
> some reason.
>
> Suggestions?
> --
> -------------------------------------------------------------
> Scott Haneda Tel: 415.898.2602
> http://www.newgeo.com Fax: 313.557.5052
> [EMAIL PROTECTED] Novato, CA U.S.A.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
