Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: select conditional aggregates

    I want to create a query to summarize sales for each of the last five weeks and group by product type ie.

    select product, sum(amount this week), sum of (amount last week) ... group by product

    my sales table looks like this

    product date amount
    x d1 xx
    x d2 xx
    y d3 xx
    y d4 xx
    x d5 xx

    How can this be done?

    thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT Product
    , SUM(CASE WHEN SELECT DATEDIFF(ww,DATEADD(dd,0,GetDate()-7),DATEADD(ww,0,GetDate()))=1 THEN Amount Else0 END) AS Week1

    , SUM(CASE WHEN SELECT DATEDIFF(ww,DATEADD(dd,0,GetDate()-7),DATEADD(ww,0,GetDate()))=2 THEN Amount Else0 END) AS Week2

    ect
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    See this other post on CROSSTAB queries, very similar to what you are trying to do.

    http://www.dbforums.com/t978537.html
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2004
    Posts
    3
    2 replies in less than 30 minutes??????????
    thanks a million
    but brett shouldne there be a space between else and 0 or is it else0 ?
    thanks again

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by caisys
    2 replies in less than 30 minutes??????????
    thanks a million
    but brett shouldne there be a space between else and 0 or is it else0 ?
    thanks again
    Damn straight!

    I didn't test this one....

    My Bad....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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