Forum Stats

  • 3,759,185 Users
  • 2,251,510 Discussions
  • 7,870,528 Comments

Discussions

Aggregate function -Avg is not working in my sql query

Gayathri Venugopal
Gayathri Venugopal Member Posts: 108
edited Jun 30, 2015 8:00AM in SQL & PL/SQL

Hi ,

In my query I need to display date and  average age:

SELECT  (SYSDATE-rownum) AS DATE,

avg((SYSDATE - rownum)- create_time) as average_Age

FROM items

group by (SYSDATE-rownum)

output will be something like:

Date_in         Average   Age

24/JUN/15         20

23/JUN/15         19

22/JUN/15         18

But my output for average age is not correct.It's simply calculating/displaying  the output of  (SYSDATE - rownum)- create_time but not calculating the average of them though i use -

avg((SYSDATE - rownum)- create_time) .

Can someone tell me why the aggregate function AVG is not working in my query and what might be the possible solution

Tagged:
John StegemanFrank Kulash

Answers

  • RogerT
    RogerT Member Posts: 1,853 Gold Trophy
    edited Jun 30, 2015 7:38AM

    Define "not working"

    My assumption is:

    CREATE_TIME is a date column

    So you do (DATE - NUMBER) - DATE  and you get difference in days as a number datatype. Avg would do an average on all those number values...

    hth

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jun 30, 2015 8:00AM

    Rownum is evaluated as a constant for each row. It's not doing what you think it's doing. You cannot use rownum in that way (to get the average of a set)

    Edit: I was on the wrong path with this statement Didn't notice the group by... Frank has hit it.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    edited Jun 30, 2015 7:53AM

    Hi,

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

    Explain, using specific examples, how you get those results from that data.

    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

    See the forum FAQ: 

    Gayathri Venugopal wrote:
    
    Hi ,
    In my query I need to display date and  average age:
    
    SELECT  (SYSDATE-rownum) AS DATE,
    avg((SYSDATE - rownum)- create_time) as average_Age
    FROM items
    group by (SYSDATE-rownum)
      
    output will be something like:
    
    Date_in         Average   Age
    24/JUN/15         20
    23/JUN/15         19
    22/JUN/15         18
    
    But my output for average age is not correct.It's simply calculating/displaying  the output of  (SYSDATE - rownum)- create_time but not calculating the average of them though i use -
    avg((SYSDATE - rownum)- create_time) .
    
    
    Can someone tell me why the aggregate function AVG is not working in my query and what might be the possible solution 
    

    Actually, that is calculating AVG correctly.  SYSDATE is the same for every row, and ROWNUM is different for every row, so SYSDATE - ROWNUM will be different for each row.  GROUP BY SYSDATE - ROWNUM, then, means that each group will have exactly 1 row in it. 

    What results were you expecting?  How many groups do you want, and how many input rows will be in those groups?

    John Stegeman
  • odie_63
    odie_63 Member Posts: 8,439 Bronze Badge
    edited Jun 30, 2015 7:56AM
    Can someone tell me why the aggregate function AVG is not working in my query and what might be the possible solution

    Before suggesting a solution, we have to understand what the problem is.

    Please post some sample data and expected output, along with the logic.

    Frank Kulash
This discussion has been closed.