Page 1 of 1

Sum Date issue

Posted: Fri Jun 24, 2011 9:30 am
by Flashart
Hi everyone

I am trying to sum and return weekly totals. Here is my code

Code: Select all

select account, campaign, SUM(clicks)clicks, SUM(impressions)impressions, AVG(avg_position)avg_pos, SUM(cost)cost, SUM(conv_mpc)conversions, 
CONCAT(date, ' -', date + INTERVAL 6 DAY) as WEEK
FROM ppc_data
where campaign ='Aftershave High'
GROUP BY WEEK(date)
order by WEEK(date)
Whilst it returns the week column with dates such as '2011-02-12-2011-02-18' it seems to sum everything regardless of the week. So rather than returning the total cost, clicks etc for that wk period, it returns the total cost as if I had done 'Select * from ppc_data'

Can anyone tell me why it's not grouping the summed totals per wk?

Thanks
Peter

Re: Sum Date issue

Posted: Mon Jun 27, 2011 4:25 am
by VladSun
1. Usually a GROUP BY SQL query requires that all none-aggregate function columns in the SELECT clause should be specified into the GROUP BY clause. I .e. in your case it should be something like this:

Code: Select all

select 
	`account`, 
	`campaign`, 
	SUM(`clicks`) as `clicks`, 
	SUM(`impressions`) as `impressions`, 
	AVG(`avg_position`) as `avg_pos`, 
	SUM(`cost`) as `cost`, 
	SUM(`conv_mpc`) as `conversions`, 
	MIN(`date`) as `week_start_date`
FROM 
	ppc_data
where 
	campaign ='Aftershave High'
GROUP BY 
	`account`, 
	`campaign`, 
	WEEK(`date`)
though it will GRUOP BY not only by week, but also by account and campaign. I think it's not the desired result, so you may want to remove account and campaign columns from SELECT and GROUP BY clause.

2. Always put column names in backsticks (is that the proper word???) like I did in the above query. WEEK, DATE are SQL keywords ...

3. Avoid doing output formatting in SQL queries ( CONCAT(date, ' -', date + INTERVAL 6 DAY) )

Re: Sum Date issue

Posted: Mon Jun 27, 2011 6:55 am
by Flashart
Hi Vladsun

That's really interesting, thanks. Is there anyway I can get it to run from the monday as it returns the week starting on sunday?

I should have made this clear originally for which I apologise! Thank you for your help!

Re: Sum Date issue

Posted: Mon Jun 27, 2011 7:06 am
by VladSun
Flashart wrote:Is there anyway I can get it to run from the monday as it returns the week starting on sunday?
The manual is your friend:

http://dev.mysql.com/doc/refman/5.5/en/ ... ction_week

Re: Sum Date issue

Posted: Mon Jun 27, 2011 7:11 am
by Flashart
Thanks for your help!

Re: Sum Date issue

Posted: Mon Jun 27, 2011 9:51 am
by Flashart
Hmm seems I can't quite get the syntax right.

If I do WEEK (`date`, 1) as `week_start_date` instead of MIN(`date`) AS `week_start_date` it returns the week number. If I do MIN(`date`,1) AS `week_start_date` I get an error.

Clearly i am doing something wrong but not sure how to fix it? I would appreciate your help!

Re: Sum Date issue

Posted: Mon Jun 27, 2011 3:26 pm
by VladSun
I thought, you needed the date periods and not the week numbers. You don't need to use the WEEK function in the SELECT clause, even though you GROUP BY it. That''s why I put the `date` column into an aggregate function - in order to get the start date of the "grouping". If you use MAX you will get the end date:

Code: Select all

SELECT 
         ...
         MIN(`date`) AS `week_start_date`,
         MAX(`date`) AS `week_end_date`
FROM 
         ...
GROUP BY 
         WEEK(`date`, 1)

Re: Sum Date issue

Posted: Mon Jun 27, 2011 4:20 pm
by Flashart
Thank you ever so much. You have filled a gap in my understanding so I sincerely thank you! I am a complete beginner with this so a lot of concepts are very new to me. You were right, I needed to show the date period not the week number. I mentioned that purely as I was trying out the mysql documentation code.

Many thanks.

Re: Sum Date issue

Posted: Mon Jun 27, 2011 4:48 pm
by superdezign
VladSun wrote:2. Always put column names in backsticks (is that the proper word???) like I did in the above query. WEEK, DATE are SQL keywords ...
*backticks ;)
Backtick in programming, grave in speech.

Re: Sum Date issue

Posted: Mon Jun 27, 2011 5:28 pm
by VladSun
superdezign wrote:
VladSun wrote:2. Always put column names in backsticks (is that the proper word???) like I did in the above query. WEEK, DATE are SQL keywords ...
*backticks ;)
Backtick in programming, grave in speech.
I was pretty sure I wrote it wrong :) Thanx for correcting me :)