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