List: | General Discussion | « Previous MessageNext Message » | |

From: | Christopher R. Jones | Date: | January 31 2000 11:03pm |

Subject: | Re: Mathematical Problem :( | ||

View as plain text |

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

Thread | ||
---|---|---|

• Mathematical Problem :( | Craig Vincent | 1 Feb |

• Re: Mathematical Problem :( | Faisal Nasim | 1 Feb |

• Re: Mathematical Problem :( | Craig Vincent | 1 Feb |

• Re: Mathematical Problem :( | Christopher R. Jones | 1 Feb |