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