[thelist] Re: php/mysql: fetch records posted this week and add values

Dunstan Orchard dunstan at 1976design.com
Wed Oct 22 06:51:33 CDT 2003


> Dunstan asked:
> I need to write a query which selects only the records whose blog_date 
> value falls in this week. I then need to get the sum of all the values 
> of blog_num for those records.


> From: "Luther, Ron" <ron.luther at hp.com>:
>
> Select SUM(blog_num) as SUMMER from blog=20
> WHERE blog_date BETWEEN '06-OCT-03' and '12-OCT-03';


> From: Anthony Baratta <Anthony at Baratta.com>:
>
> select blog_date, blog_num where blog_date between 'mm/dd/yy' AND 'mm/dd/yy'
> select sum(blog_num) where blog_date between 'mm/dd/yy' AND 'mm/dd/yy'


> From: Hassan Schroeder <hassan at webtuitive.com>:
>
> SELECT SUM(blog_num) FROM blog WHERE -- eh? Does "in this week" mean
> the last seven days, or since Monday, or since Friday, or??
> 
> And is it really almost Christmas in Dorset?  :-)


> From: Marek Kilimajer <kilimajer at webglobe.sk>:
>
> SELECT SUM(blog_num) FROM blog GROUP BY WEEK(blog_date) HAVING 
> WEEK(blog_date)=WEEK(NOW())


I should hasve said that I didn't want to faff around clculating and adding in
the BETWEEN dates. I was sure there must have been an automatic way of doing it.
Marek's reply (which didn't work but did point me in the right direction) led me
to this:

// count the press ups done this week (week = mon-sun)
$pups = mysql_result(mysql_query("SELECT SUM(blog_pups_num) FROM blog_pups WHERE
WEEK(blog_pups_date) = WEEK(NOW())"), 0);

(Actually, I've got to check if the week runs mon-sun or sun-sat in mysql, but
either way, it's a nice efficient way to doing what I wanted.)

Many thanks for the replies.

Viva Los Evolters.

- Dunstan

---------------------------
Dorset, England
http://www.1976design.com/
http://www.1976design.com/blog/


More information about the thelist mailing list