GROUP BY

------=_NextPart_000_0003_01CA1E91.A2DFD630
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I run a once daily e-mail list.

The users are able to choose the time of day they receive their daily =
e-mail. Their preference is stored in the email-delivery_hour field.

I am trying to determine which email_delivery_hour between 0 and 6 has =
the lowest number of subscribers. (I am trying to make it so the same =
number of e-mails are sent out every hour by offering this as the =
default when people sign up.) The GROUP BY section on mySQL's web site =
didn't make sense to me. I have attempted to start making the query =
(below). What additional changes to do I need to make?

SELECT * FROM `member_subscriptions` WHERE `list` =3D1 AND =
`email_delivery_hour` <=3D6 GROUP BY `email_delivery_hour` ASC ORDER BY =
`email_delivery_hour` ASC LIMIT 1

Ron
------=_NextPart_000_0003_01CA1E91.A2DFD630--

Re: GROUP BY

Ron Piggott wrote:
> I run a once daily e-mail list.
>
> The users are able to choose the time of day they receive their daily e-mail. Their preference is stored in the email-delivery_hour field.
>
> I am trying to determine which email_delivery_hour between 0 and 6 has the lowest number of subscribers. (I am trying to make it so the same number of e-mails are sent out every hour by offering this as the default when people sign up.) The GROUP BY section on mySQL's web site didn't make sense to me. I have attempted to start making the query (below). What additional changes to do I need to make?

group by is for aggregation.

eg you want to know the number of articles per category:

select category_id, count(article_id) from articles group by category_id;

so you have to use an aggregate function (count, sum, avg) and group by
your unit field.

In your case you want to get the number of subscribers (what you want to
aggregate on) per email_delivery_hour (your unit field) which translates to:

select
count(member_id), <-- guessed the fieldname. Adjust it
email_delivery_hour
from
member_subscriptions
where
list=1 and
email_delivery_hour <= 6
group by
email_delivery_hour
order by
email_delivery_hour
;

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: GROUP BY

ASC after GROUP BY ?? didn't mysql throw an error ?

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: GROUP BY

The query I am using:

SELECT COUNT(`member_reference`), `email_delivery_hour` FROM
`member_subscriptions` WHERE `list` =1 and `email_delivery_hour` <= 6 GROUP
BY `email_delivery_hour` ORDER BY COUNT(`member_reference`) ASC LIMIT 1

----- Original Message -----
From: "kranthi"
To: "Chris"
Cc: "Ron Piggott" ;
Sent: Monday, August 17, 2009 6:00 AM
Subject: Re: [PHP-DB] GROUP BY


> ASC after GROUP BY ?? didn't mysql throw an error ?


------------------------------------------------------------ --------------------



No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.392 / Virus Database: 270.13.58/2306 - Release Date: 08/16/09
06:09:00


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php