1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Query help based on groups

Discussion in 'Databases' started by UCDaZ, Jun 25, 2010.

  1. #1
    I have the following data in my database:

    SALES
    DATE SID Sales
    1/1/2008 111 1
    1/1/2008 111 1
    1/1/2008 222 1
    1/1/2008 333 1

    1/2/2008 111 0
    1/2/2008 222 4
    1/2/2008 222 7
    1/2/2008 333 9

    1/3/2008 111 6
    1/3/2008 111 4
    1/3/2008 222 1
    1/3/2008 333 8


    I need to one query to get Results 1 and one query to get Results 2:

    Results 1
    DATE SID Total Sales
    1/1/2008 111 2
    1/1/2008 222 1
    1/1/2008 333 1

    1/2/2008 111 0
    1/2/2008 222 11
    1/2/2008 333 9

    1/3/2008 111 10
    1/3/2008 222 1
    1/3/2008 333 8

    Results 2
    DATE SID Cumulative Total Sales
    1/1/2008 111 2
    1/1/2008 222 1
    1/1/2008 333 1

    1/2/2008 111 2
    1/2/2008 222 12
    1/2/2008 333 10

    1/3/2008 111 12
    1/3/2008 222 13
    1/3/2008 333 18

    Notice how Results 2 is the cumulative of the previous dates from Result 1's rows for each SID.

    Any ideas or help is greatly appreciated.
    Thanks!
     
    UCDaZ, Jun 25, 2010 IP
  2. tnrsr

    tnrsr Peon

    Messages:
    14
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    -- Result 1's query;
    Select DATE, SID, SUM(SALES) as SALES
    From #Sales
    Group By DATE, SID
    Order By DATE, SID
    ----------------------------------------------------
    -- Result 2's query;
    Select t1.DATE, t1.SID, Isnull(SUM(t1.SALES) + (
    Select SUM(SALES) as CumulativeSales
    From #Sales
    Where DATE < t1.DATE and SID = t1.SID
    Group by SID
    ), SUM(t1.SALES)) as CumulativeSales
    From #Sales t1
    Group By t1.DATE, t1.SID
    Order By t1.DATE, t1.SID
     
    tnrsr, Jun 25, 2010 IP
  3. UCDaZ

    UCDaZ Active Member

    Messages:
    180
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #3
    Thanks tnrsr.
    The first query worked, but for the second query
    I get the following error telling me to check the syntax used near 'SUM(t1.Sales)) as CumulativeSales from #Sales t1 Group By t1.DATE, t1.SID"

    Any ideas what could be wrong?

    Thanks again!
     
    UCDaZ, Jun 26, 2010 IP
  4. tnrsr

    tnrsr Peon

    Messages:
    14
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    I got the exact "Result 2" results that you asked with executing this query. I'm pretty sure that you did it but, you changed the query towards your tables right? I mean you replaced #Sales with XXXXX (your table name)..
     
    tnrsr, Jun 27, 2010 IP
  5. UCDaZ

    UCDaZ Active Member

    Messages:
    180
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #5
    Yeah I replaced #Sales with my table name
    Do you know what's wrong?
     
    UCDaZ, Jun 27, 2010 IP
  6. tnrsr

    tnrsr Peon

    Messages:
    14
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Honestly, i don't. Cause i didn't get any errors like you said. I got the exact result that you asked.
     
    tnrsr, Jul 2, 2010 IP