1

I am attempting to create a procedure that selects ads from a database in rotation. And ultimately returns the selected ads row and increments the views value.

The part I am currently working on should

  1. Pull results from the 'ads' table WHERE the 'city' matches the given city and the 'spot' matches the given spot name.
  2. Add an additional 'num' column that enumerates the resulting rows.
  3. From these results, select the row where the 'num' column matches SUM('views') % COUNT(id)

This should allow me to display the ads in rotation as long as I increment the views column when an ad is ultimately selected and returned

The code I am currently working on is....

SET @t1=-1;

SELECT * 
  FROM (SELECT @t1 := @t1+1 AS num, ads.* 
          FROM ads 
         WHERE city = 'Maitland' 
           AND spot = 'home-banner' 
      ORDER BY id ASC) dt 
 WHERE dt.num = SUM(dt.views) % COUNT(dt.id);

... However I am having problems with the SUM and COUNT functions. I get an invalid use of group function error. I have also tried moving the functions to inside the sub-query, but for some reason this causes the sub-query to only return one result.

Results of Sub Query

num id  spot    city    views
0   1   home-banner Maitland    0
1   2   home-banner Maitland    2
2   3   home-banner Maitland    0
3   4   home-banner Maitland    0
4   5   home-banner Maitland    0
5   6   home-banner Maitland    0
hakre
  • 193,403
  • 52
  • 435
  • 836
christian
  • 2,279
  • 4
  • 31
  • 42

1 Answers1

1

Try this:

SELECT * FROM (
  SELECT @t1 := @t1 + 1 AS num, ads.*
  FROM ads, (SELECT @t1 := -1) init
  WHERE city = 'Maitland' AND spot = 'home-banner'
  ORDER BY id ASC
) dt
WHERE dt.num = (
  SELECT SUM(views) % COUNT(id) FROM ads
  WHERE city = 'Maitland' AND spot = 'home-banner')
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • Thanks, but wouldn't this calculate the modulus of views and count for the whole ads table, not the results of the subquery? I'm only concerned about the results that match the city and spot. I guess I could repeat the WHERE clause, but wouldn't that be double duty performance wise? – christian Mar 26 '12 at 01:29
  • You're right, I missed the WHERE clause. And yes it will take longer, but that's the only way I can think of :( You could also create a temporary table and store all the current city and spot data and then query that table to get the `sum` and `count` – Mosty Mostacho Mar 26 '12 at 01:38
  • 1
    Okay, hopefully it wont be too bad in practice. Thanks for the temporary table suggestion, perhaps that would be a better route. Ultimately I am going to be pulling multiple ad spots (10 or so) for the same city. – christian Mar 26 '12 at 01:41