Sum Date issue

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Sum Date issue

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Sum Date issue

Post 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) )
There are 10 types of people in this world, those who understand binary and those who don't
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Sum Date issue

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Sum Date issue

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Sum Date issue

Post by Flashart »

Thanks for your help!
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Sum Date issue

Post 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!
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Sum Date issue

Post 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)
There are 10 types of people in this world, those who understand binary and those who don't
Flashart
Forum Commoner
Posts: 71
Joined: Tue Oct 06, 2009 12:12 pm

Re: Sum Date issue

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Sum Date issue

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Sum Date issue

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply