SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    pg for mild peril cow's Avatar
    Join Date
    Mar 2000
    Location
    nevada, usa
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL help: SUM & GROUP BY functions

    i have a page that displays the last 5, 10 or 20 days of sales. the statement looks like this:

    Code:
    SELECT TOP "+date_range+" SalesDate, SUM(DailySales) AS total_sales
    FROM Sales
    GROUP BY SalesDate
    ORDER BY SalesDate DESC
    the resulting table looks like this when date_range = 5:

    Code:
    SalesDate	total_sales
    06/25/2003	17
    06/24/2003	25
    06/23/2003	35
    06/20/2003	11
    06/19/2003	22
    the problem is that i also want to display the sum of each day's total_sales. any ideas??? i've tried including a subquery, but that doesn't allow me to sum up group calculations...tia!
    "There's no justice like angry mob justice!" --Seymour Skinner

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,254
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Just do a select sum(DailySales) where daterange < CalculatedDate

    You can calculate the date difference in the SQL statement, but I'm having a brainfade right now, so I'll look at it again tomorrow.....bedtime...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    pg for mild peril cow's Avatar
    Join Date
    Mar 2000
    Location
    nevada, usa
    Posts
    636
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the only problem is that my dates are strings, not ints. so i can't calculate the dates.
    "There's no justice like angry mob justice!" --Seymour Skinner

  4. #4
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by cow
    the only problem is that my dates are strings, not ints. so i can't calculate the dates.
    Use cast(columnname as type) if you want to change the type of a column on the fly. Let's say that some newbie DBA set up your dailysales field as a varchar() instead of a currency or numeric data type. You can change it like so:
    Code:
    SELECT SUM(CAST(DailySales AS money)) 
    FROM tablename;


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
  •