Geek asks and answers

Getting min () of a count (*) column

I have a table called Vehicle_Location containing columns (or more):

ID               NUMBER(10)
SEQUENCE_NUMBER NUMBER(10) 
TIME            DATE

and I'm trying to get the minimum / maximum / average number of entries per day for each identifier.

I still have

select id, to_char(time), count(*) as c
  from vehicle_location
 group by id, to_char(time), min having id = 16

which gives me:

ID                     TO_CHAR(TIME) COUNT(*)               
---------------------- ------------- ---------------------- 
16                     11-05-31      159                    
16                     11-05-23      127                             
16                     11-06-03      56

So, I would like to get the min / max / avg column of count (*). I use Oracle as my DBMS.

+3
source share
1 answer

I don't have an oracle station to test, but you can just wrap the aggregator around yours SELECTas a subquery / view / inline view

So that would be (WRONG !!)

SELECT 
    AVG(s.c)
    , MIN(s.c)
    , MAX(s.c)
    , s.ID
FROM
    --Note this is just your query
    (select id, to_char(time), count(*) as c from vehicle_location group by id, to_char(time), min having id = 16) as s
GROUP BY s.ID

Here are some readings on it:
http://www.devshed.com/c/a/Oracle/Inserting-SubQueries-in-SELECT-Statements-in-Oracle/3/

:. , MIN, MAX .

EDIT2: min/max , RDBMS ( oracle) . , , , MIN, MAX, , .
: http://momendba.blogspot.com/2008/07/min-and-max-functions-in-single-query.html

+8

More articles:


All Articles