Hello,
I am having a lot of trouble trying to find the best
way to write the SQL to do the following:
I have a table called "referrals." In this table I
have a column called "ref." The data in the ref column
are of the form "p/$promo_id/$date". An example would
be "p/26/20010629" which says promotion number 29
occuring on June 29th 2001.
My task is to find a way to do a group by of all the
promotion IDs within a certain date range. The
following code is INCORRECT (BETWEEN 'p/%/2001...' IS
NOT VALID), but gives a good picture of what I am
trying to do:
SELECT ref,SUM(clicks) AS num_clicks FROM referrals
WHERE ref BETWEEN 'p/%/20010601' AND 'p/%/20010630'
GROUP BY ref
The code above is meant to do a group by of all the
clicks generated by each promotion occuring between
June 1 and June 30
I realize that I could hypothetically loop over each
promotional ID and do ref BETWEEN 'p/3/XXX' AND
'p/3/YYY',....,p/888/XXX AND p/888/YYY', but this
method is very bad in performance when the promotion
IDS goes into the tens of thousands, so the GROUP BY
is important to keep for me.
Is there a way to account for the date range WITHOUT
having to create a new date column which copies the
date over from ref codes in the column ref?
My Best Wishes.
__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
