CODEWITHSUNDEEP
sqloracle-databasegroup-by
Mr Man
Mr Man

Reputation: 1588

Query with inner select and Group by

I am struggling with this query, here is the table set up:

date       | time | count
----------------------------
12/12/2015 | 0:00 | 8
12/12/2015 | 1:00 | 19
12/12/2015 | 2:00 | 36
12/13/2015 | 0:00 | 12
12/13/2015 | 1:00 | 22
12/13/2015 | 2:00 | 30
12/14/2015 | 0:00 | 14
12/14/2015 | 1:00 | 26
12/14/2015 | 2:00 | 38

What I would like my query to return is something like this:

date       | time | count | DAY       | AVG/HR | AVG/DAY 
---------------------------------------------------------
12/12/2015 | 0:00 | 8     | MONDAY    | 11.33  | 63
12/12/2015 | 1:00 | 19    | MONDAY    | 22.33  | 63
12/12/2015 | 2:00 | 36    | MONDAY    | 34.67  | 63
12/13/2015 | 0:00 | 12    | TUESDAY   | 11.33  | 64
12/13/2015 | 1:00 | 22    | TUESDAY   | 22.33  | 64
12/13/2015 | 2:00 | 30    | TUESDAY   | 34.67  | 64
12/14/2015 | 0:00 | 14    | WEDNESDAY | 11.33  | 78
12/14/2015 | 1:00 | 26    | WEDNESDAY | 22.33  | 78
12/14/2015 | 2:00 | 38    | WEDNESDAY | 34.67  | 78

So basically that is returning all rows (there will be months worth of data in the table, with each day having 24 records/hours). And adding a day of the week field, and an Average of the count per hour along with a average of the count per day of the week. The last 2 are what I am struggling with. Here is what I have so far:

SELECT DATE, TIME, COUNT,
     TO_CHAR(DATE, 'DAY'),
     (SELECT AVG(t2.COUNT) 
        FROM tableXX t2
       WHERE t2.time = t1.time
    GROUP BY t2.time) AS AvgPerHr
     (SELECT AVG(t2.COUNT) 
        FROM tableXX t2
       WHERE TO_CHAR(t2.DATE, 'DAY') = TO_CHAR(t1.DATE, 'DAY')
    GROUP BY TO_CHAR(t2.DATE, 'DAY')) AS AvgPerDay
FROM tableXX t1
ORDER BY DATE, TO_DATE(TIME, 'hh24:mi') ASC;

Any suggestions would be appreciated, the query above returns data, but it definitely isn't accurate.

Upvotes: 1

Views: 57

Answers (2)

padma
padma

Reputation: 21

This can be solved by using analytical functions.

SELECT DATE, TIME, COUNT,
    TO_CHAR(DATE, 'DAY'),
    AVG(t1.COUNT) 
        OVER (PARTITION BY TIME) AS AvgPerHr,
    AVG(t1.COUNT) 
        OVER (PARTITION BY TO_CHAR(DATE, 'DAY'))  AS AvgPerDay
FROM tableXX t1
ORDER BY DATE, TO_DATE(TIME, 'hh24:mi') ASC;

Upvotes: 2

krokodilko
krokodilko

Reputation: 36107

Try:

SELECT "DATE", "TIME", "COUNT", TO_CHAR(DATE, 'DAY') "DAY,
       avg( "COUNT" ) Over (partition by  "TIME" ) "AVG/HR",
       SUM( "COUNT" ) Over (partition by  "DATE" )  "AVG/DAY"
FROM tablexx
ORDER BY 1;

I use SUM( "COUNT" ) instead of AVG( "COUNT" ), since 63 in the first row of your example appears to be sum per day, not an average.

Upvotes: 1

Related Questions