Try this:
mysql> create table temptable ( cnt Float(4));
mysql> insert into temptable (cnt) select sum(displays) from banadsplit;
mysql> select temptable.cnt, banadsplit.account, banadsplit.percentage,
banadsplit.displays
from temptable, banadsplit GROUP BY account HAVING percentage/100 <=
displays/cnt;
+--------+---------+------------+----------+
| cnt | account | percentage | displays |
+--------+---------+------------+----------+
| 100.00 | 2 | 5.00 | 35 |
| 100.00 | 3 | 10.00 | 15 |
+--------+---------+------------+----------+
2 rows in set (0.01 sec)
mysql> drop table temptable;
mysql> quit;
>*sigh*
>Here's the new issues:
>
>mysql> SELECT account, SUM(displays) as cnt,percentage,displays FROM
>banadsplit GROUP BY account HAVING percentage <= displays/cnt;
>Empty set (0.00 sec)
>
>Nothing is displayed (but no errors this time)
>
>mysql> select * from banadsplit;
>+-----+---------+----------+------------+
>| uid | account | displays | percentage |
>+-----+---------+----------+------------+
>| 1 | 1 | 50 | 60.00 |
>| 2 | 2 | 35 | 5.00 |
>| 3 | 3 | 15 | 10.00 |
>+-----+---------+----------+------------+
>3 rows in set (0.00 sec)
>
>This is my test information...according to my formula...account #1 should be
>true while the other two are false. Since the max displays for account 1 is
>60, for account 2 is 5 and account 3 is 15 (for the current total number of
>displays).
>
>mysql> SELECT account, SUM(displays) as cnt,percentage,displays FROM
>banadsplit GROUP BY account;
>+---------+------+------------+----------+
>| account | cnt | percentage | displays |
>+---------+------+------------+----------+
>| 1 | 50 | 60.00 | 50 |
>| 2 | 35 | 5.00 | 35 |
>| 3 | 15 | 10.00 | 15 |
>+---------+------+------------+----------+
>3 rows in set (0.01 sec)
>
>For some reason the SUM(displays) as cnt only calculates the displays value
>for that particular row...it doesn't actually sum up all the rows' values.
>
>> However, there has to be a cleaner solution -- you should probably not
>even be
>> dealing with percentage the way it is if you are using GROUP BY, as the
>value of
>> percentage would be ambiguous -- (if I am grouping by account, and there
>are
>> several different percentage values for the same account, which one do I
>choose
>> ? )
>
>That in itself is fine (at least for my situation)...there will only be one
>listed percentage for each account EVER.
>
>> What is the purpose/meaning of percentage anyway?
>
>The meaning of the table is this...I have a site where the rotating
>ads are spread out to all my clients...on a daily basis the
>percentage of ads to be displayed are calculated based on other criteria and
>are put into this table.
>
>In my case
>account stands for the client's ID #
>displays # of ads displayed on behalf of the client
>percentage The percentage of the displays that should be used for the
>client
>
>So if say there are 100 displays done...in my test 60 displays should go to
>account 1, 35 go to account 2 and 15 go to accound three...now obvious in
>the case where there's a remainder involved with the split these numbers
>maybe be off by 1 or 2...but that's not a big deal... Although I could have
>a series of queries calculate whose ad should be displayed ...because of the
>nature of this program (web based dynamic HTML) ...speed is of the essence
>so a single query having MySQL return all potential candidates and stripping
>out the ones that have already received their fair share is essential. The
>reason for the percentage setup is that the total number of displays is
>always a variable and more than like will be different for every single
>query...this setup should theoretically ensure that all clients are
>promoted under the appropriate terms.
>
>Sincerely,
>
>Craig Vincent
>
Christopher R. Jones, P.Eng.
14 Oneida Avenue
Toronto, Ontario M5J 2E3
Tel. 416 203-7465
Fax. 416 203-3044
Email cj@stripped
