SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Statement Help

    Hello ...

    I am trying to SUM one column between 2 dates and I can not get it to work

    Code:
    SELECT  SUM(order_amount) as "Total Salary", account, order_date
    FROM orders Where account = 94 AND order_date BETWEEN 1/1/2007 AND 12/31/2007
    Group By account, order_date
    Could someone tell me what I am doing wrong?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    SELECT SUM(order_amount) as "Total Salary"
    FROM orders
    Where account = 94
    AND order_date BETWEEN '2007-01-01' AND '2007-12-31'

    Simply saying your query 'doesn't work' isn't very clear. You should tell us what the problem is. Does it give an error? Then tell us what the error is. Does it give a result, but it isn't what you want? Then tell us what that wrong result is, and what it should be.

    I changed the date format in the WHERE statement. It might be the cause of your problems. I also took away the account and order_date field in the SELECT. You only select one account, so there's no need to get that field. And if you want to sum the order amounts between two order dates, then you shouldn't put that date field in the SELECT and GROUP BY, because that way you'll sum the order amounts of every single day in the given period, and not the total of that period.
    I hope I made myself clear

  3. #3
    SitePoint Guru mdumka's Avatar
    Join Date
    Jul 2003
    Location
    True North
    Posts
    642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That got it working for me ... thanks alot ...

    I was not getting an error but just an empty result set ...

    Thanks again

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by guido2004 View Post
    I changed the date format in the WHERE statement. It might be the cause of your problems. I also took away the account and order_date field in the SELECT. You only select one account, so there's no need to get that field. And if you want to sum the order amounts between two order dates, then you shouldn't put that date field in the SELECT and GROUP BY, because that way you'll sum the order amounts of every single day in the given period, and not the total of that period.
    I hope I made myself clear
    very clear

    nicely explained

    good job

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •