Software

Ask something
Discussion Locked

summary query?

0 helpful
3 replies

by natasha.epperson

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Right now i have a query on top on another query for a certan count. I'm wondering if there is a way to turn it into a single query.

the data is say 1,2,3 time day count.
For report/query I need to pull 1,2,3 into say 1-5 and add them together but I also need say 6-10 and so on.

I had set it up so that it's all sererpated querys cause if i pull it all together it doesn't add the times together just pulls the between count.

Hope that makes sence, I'm the worst at explaining myself lol

3 replies

by tony hopkinson

if I'm understanding you correctly you need a bit of arithmetic
if x is 1,2,3... 99
(x + 1) / 5) truncated to an integer will give you
1-5 -> 1
6 - 10 -> 2
etc.
select this number and group by it and you should get want you want.

by natasha.epperson

Sorry, didn't realize I had put in wrong forum.
so I'd do it as such?
IE
Time Dif
1 day
2 day
3 day

So the formula should be? (timedif+1+2+3)/3 ? I did something like this but when I say do sum by time and add in the different time betweens (ie between 1 and 5....)it won't add them together it seperates the time still and just gives me the query result set of between 1 and 5 and so on.

by tony hopkinson

Given
Table 1
Field1 Field2
1 10
2 20
6 60
7 70

Select Cast(int,((field1 + 1)/5)) as Rnge, sum(field2), count(*) From Table1 Group By Rnge
would give
1,30,2
2,130,2

This discussion is closed to new replies.