CODEWITHSUNDEEP
sqloracle-databasegroupingrow-number
user3012768
user3012768

Reputation: 41

Grouping a concrete number of rows in a SQL query

I want to group data without any specific criteria, just the number of data for each resultant group. I have a table like this:

DATE              VAL1      VAL2
------------    ------    ------
01-01-2013           5         8
01-02-2013          14        23
01-03-2013          10         6
01-04-2013          21        88
01-05-2013           9        11
01-06-2013           4         9
01-07-2013          19        42
01-08-2013           8         4
01-09-2013          12         1
01-10-2013           2         8
01-11-2013          31        65
01-12-2013           3         6
...

Think that date field could be, for example, a number and not a date...

What I want is, for example, get the total sum or average of groups of data, where groups have a specific number of rows (the same number for all groups).

For example, for three rows per group, where I want get the total sum of VAL1 and the average of VAL2:

INTERVAL                  SUM VAL 1   AVG VAL 2
-----------------------   ---------   ---------
01-01-2013 - 01-03-2013          29        12.3
01-04-2013 - 01-06-2013          34        33.3
01-07-2013 - 01-09-2013          39        15.6
01-10-2013 - 01-12-2013          36        26.3
...

I really think it's possible to do with a query, but I can't find the way to get the proper "group by" sentence. Can somebody help me?

Thanks a lot in advance!

Upvotes: 4

Views: 578

Answers (3)

Noel
Noel

Reputation: 10525

You can use row_number function divided by 3 to assign unique number to each group of 3 consecutive rows. Then, you can aggregate on this group number.

select min("DATE") ||'-'||max("DATE"),
       sum(val1),
       avg(val2)
from (
      select "DATE", val1, val2,
             ceil(row_number() over (order by "date") / 3) as grp
      from mytab
    ) as x
group by grp
order by grp;

Upvotes: 3

TechDo
TechDo

Reputation: 18629

Please try:

select  
  min("DATE")||' - '||max("DATE") "Interval",
  sum(Val1) "SUM VAL 1", 
  cast(avg(Val2) as numeric(18,1)) "AVG VAL 2"
from(
  select 
    "DATE",
    ceil(extract(month from "DATE")/3) dt,
    Val1,
    Val2
  from 
    YourTable 
)x 
 group by dt 
 order by dt

SQL Fiddle Demo

Upvotes: 1

user2756339
user2756339

Reputation: 705

The ROWNUM pseudo column in oracle or the LIMIT of mysql could help you acheive it.

I think what you mean is pagination. Given in this link. http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Upvotes: 0

Related Questions