SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gloosemo View Post
    OK i have a table called consumption.
    columns 3, 4, 5, and 6 are superfluous, unnecessary, extraneous, redundant, and ... um ....

    ... what was your question again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •