CODEWITHSUNDEEP
sqloracle-databaseaverage
Landon Statis
Landon Statis

Reputation: 839

Oracle Average Query

One would think this would be simple. I just want the date, count & average per day for whatever time range. So, in the example below, I want the count & average per day over the 10 day period.

SELECT date_added, 10 / customer_cnt AS average
FROM (SELECT TRUNC(date_added) AS date_added, 
             ROW_NUMBER() OVER (PARTITION BY TRUNC(date_added) ORDER BY TRUNC(date_added)) AS  rnum,
             COUNT(*) OVER (PARTITION BY TRUNC(date_added)) AS customer_cnt
     FROM payments_log
     WHERE action = 'get_cc_info'
       AND TRUNC(date_added) >= TRUNC(SYSDATE) - 10)
WHERE rnum = 1;

But I'm not getting anything close to what I expect.

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269783

I don't know what "average per day" means. I think you just want the count:

SELECT TRUNC(date_added) AS date_added, 
        COUNT(*) as day_count
FROM payments_log
WHERE action = 'get_cc_info' AND
      date_added >= TRUNC(SYSDATE) - INTERVAL '10' day
GROUP BY TRUNC(date_added);

If you want the average during this period (i.e. one row in the result set), then you want something like this:

SELECT COUNT(*) / 10 as daily_average
       COUNT(*) / COUNT(DISTINCT TRUNC(date_added) daily_average_on_days_with_data
FROM payments_log
WHERE action = 'get_cc_info' AND
      date_added >= TRUNC(SYSDATE) - INTERVAL '10' day;

Upvotes: 1

Related Questions