Need a complex query but can't think of how to do it

Databases
1

OK i have a table called consumption.

Column1: “Calories” (CHAR8) contains a number for each entry (1-9999). This should probably be a number data type may change if necessary.

Users make multiple daily entries into the table for Calories and each entry is essentially timestamped with the date, year, month, day, and weekday columns as shown below"

Column2: “date” (DATE) contains the date of the entry in format YYYY/MM/DD
Column3: “year” (INT) contains the year of the entry (2011)
Column4: “month” (TINYINT) contains the month of the entry (1-12)
Column5: “day” (TINYINT) contains the day of the entry (1-31)
Column6: “weekday” (CHAR10) contains the name of the day of entry, i.e. “Mon”, “Tues”, etc

Suppose a user of the DB wants to find out the total cal consumed for each of the 4 weeks just passed, with the last of the 4 weeks ending on the most recent Sunday just passed and beginning on the Monday before that Sunday, so each week runs Monday to Sunday.

The only way i can think of doing it is multiple queries, each a SUM for the calories column where the date is greater than a certain date and less than a certain date. But that means there would be 4 separate queries. The user may want to find this information for up to the past 52 weeks, meaning i would need 52 queries. There must be a way to accomplish it all with a single query.

Thanks in advance, G

2

columns 3, 4, 5, and 6 are superfluous, unnecessary, extraneous, redundant, and … um …

… what was your question again?

3

As I’ve p osted to someone else tonight, when you have a question you post in a forum, it isn’t necessary to post it in other forums just because you can. Post a question, if you don’t get a response in a reasonable period of time (think 24 hours) then consider posting elsewhere.