Sum and Group by

  • Hello,

    I am not sure where I can find anything about grouping by number. To be more specific :

    Table: 2 columns

    Number of the week                  Amount                  

    1                                               50

    2                                               25

    3                                               47

    4                                               15

    5                                                5

    6                                                87

    7                                                26

    8                                                105

    9                                                 67

    10                                                18

     

    Now I need to get SUM() for every 3 weeks or 6 weeks or any number I need. Can not find how to group by number.

    THANKS!

  • GROUP BY (Number_of_the_week - 1)/3

    GROUP BY (Number_of_the_week - 1)/6

    _____________
    Code for TallyGenerator

  • If the sample data you posted is as you have it in your table I'm not sure why you need to "group by" at all...

    can you not just "select sum(amount) from table where number_of_week <= 3" ?!

    or if you are in between weeks you could do

    "select sum(amount) from table where number_of_week between 3 and 6"







    **ASCII stupid question, get a stupid ANSI !!!**

  • THANKS!

    group by (numberoftheweek-1)/3 - worked perfect!

    Now, Sergiy, why it's numberoftheweek-1? (because it starting to count from 0?)

     

  • 0,1,2 -> /3 = 0

    If you need to group weeks 1,2,3 then you need to substract 1 from those values.

    If you need to group 2,3,4 then substract 2.

    _____________
    Code for TallyGenerator

  • OK, now I am thinking if the NumberOfTheWeek is too easy to perform this procedure. What if :

    CustomerID           Amount

    12                          5

    56                          12

    51                          45

    123                         47

    456                         63

    125                          2

    10                          55

     

    If it needs to be Group by 3 records how it can be done?

     

  • Went this far

    select count(@@rowcount), sum(amount)

    from TableName

    Now - can we break the count(@@rowcount) for the Group By ?

    count(@@rowcount)/3 is not good....

  • Vichka,

    There is no such a thing as the record number conceptually in SQL. There is no implicit order on how those are stored.

    You cal always write something like

    create table #T1(i int identity(1,1), CustomerID int,  Amount dec(9,2) )

    insert into #T1( CustomerID, Amount)

    select   CustomerID, Amount from YourTable order by CustomerID

    and then preform the grouping

    select   (i-1)/3 as GroupNo , Sum(Amount)

    from #T1

    group by (i-1)/3

     

     


    * Noel

  • Thank you very much! I did it and the GROUP worked!

    I did not know about record count counting problem

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply