From: Nicolas Prade
Date: March 2 2000 3:58pm
Subject: RE: COUNTing
List-Archive: http://lists.mysql.com/mysql/29818
Message-Id: <001401bf8460$359ef540$04e12cd4@validator.cge.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit



> -----Original Message-----
> From: Graeme B. Davis [mailto:GraemeD@stripped]
> Sent: jeudi 2 mars 2000 16:19
> To: Jonathan Stimmel; mysql@stripped; ant@stripped
> Subject: Re: COUNTing
> 
> 
> Thanks for the info!
> 
> Is there a way to return this information in the same row
> 
> Right now it returns info like this:
> 
> | son          | Email  |       12 |
> | son          | Phone  |        2 |
> 
> But I would want it to return like:
> 
> | son          | 12 | 2 |
> 
> Any ideas?
> 
> > What you're really doing is looking to count groups of "person"
> > and "medium". Given that, how about something like:
> > 
> >  SELECT person, medium, COUNT(*)
> >  FROM table
> >  WHERE medium IN ('Phone', 'Email)
> >  GROUP BY person, medium;
> > 

Hello,
you can try:

SELECT person,
SUM(IF(medium='Phone',1,0)) AS s1,
SUM(IF(medium='Email',1,0)) AS s2
FROM table
GROUP BY person HAVING s1+s2>0;

I would not call this "optimized" though.
Hope this helps,
Nicolas.