i have a situation where i group by count/ day. I also want to get a average in my query. how do i get it? the sql fiddle is http://sqlfiddle.com/#!4/42960/34301

select mgr,trunc(hiredate) ,count(*)   from emp

group by mgr,trunc(hiredate); order by count(*) desc

this works and provides count per day. However, i also want to get the Average count per day which i dont know the syntax.

select mgr,trunc(hiredate) ,count() ,avg(per day) from emp group by mgr,trunc(hiredate); order by count() desc

CodePudding user response：

I wrapped your first query as a subquery, and then took the maximum and minimum hiredate to calculate the number of days.

The reason you can't just AVG() your existing count is because days that don't exist are actually 0's but you want to make sure those are included in your average.

This could get messy so the best thing I could come up with is just to divide by the max - min.

As long as the mgr is still active, it might be more accurate to do (SYSDATE - min)

http://sqlfiddle.com/#!4/42960/34312

select 
  mgr, 
  total_count / ((max_hiredate - min_hiredate)   1) as daily_avg
FROM 
  (
    select 
      mgr, 
      min(hiredate) as min_hiredate, 
      max(hiredate) as max_hiredate, 
      sum(dailycount) as total_count 
    FROM 
      (
        select 
          mgr, 
          trunc(hiredate) as hiredate, 
          count(*) as dailycount 
        from 
          emp 
        group by 
          mgr, 
          trunc(hiredate)
      ) rs 
    group by 
      mgr
  ) rs2

CodePudding user response：

Maybe I missunderstood what you are trying to get as a result as your sample data all counts return as 1. It seems to me that your want something like this out of your query (which I put as a subquery). A few analytic functions (with full dataset) give a good insight in hireings per MGR (result is at the end of code). Regards...

Select
    MGR,
    HIREDATE,
    COUNT(HIREDATE) OVER() "TOTAL_HIRE_DAYS",
    SUM(DAYLY_HIREINGS) OVER() "TOTAL_HIREINGS",
    COUNT(HIREDATE) OVER(PARTITION BY MGR ORDER BY MGR) "TOTAL_MGR_HIRE_DAYS",
    SUM(DAYLY_HIREINGS) OVER(PARTITION BY MGR ORDER BY MGR) "TOTAL_MGR_HIREINGS",
    Round((SUM(DAYLY_HIREINGS) OVER(PARTITION BY MGR ORDER BY MGR) / SUM(DAYLY_HIREINGS) OVER()) * 100, 2) "MGR_HIREING_PERCENTAGE",
    AVG(DAYLY_HIREINGS) OVER(PARTITION BY MGR, HIREDATE ORDER BY MGR, HIREDATE) "AVG_DAYLY_MGR_HIREINGS",
    AVG(DAYLY_HIREINGS) OVER(PARTITION BY HIREDATE ORDER BY HIREDATE) "AVG_DAYLY_HIREINGS"
From
    (
        select 
            mgr "MGR", trunc(hiredate) "HIREDATE", count(*) "DAYLY_HIREINGS"
        from 
            emp
        group by 
            mgr,trunc(hiredate)
        order by 
            count(*) desc
    )
Group By
    MGR, HIREDATE, DAYLY_HIREINGS
--
-- R e s u l t
--
--         MGR HIREDATE  TOTAL_HIRE_DAYS TOTAL_HIREINGS TOTAL_MGR_HIRE_DAYS TOTAL_MGR_HIREINGS MGR_HIREING_PERCENTAGE AVG_DAYLY_MGR_HIREINGS AVG_DAYLY_HIREINGS
--  ---------- --------- --------------- -------------- ------------------- ------------------ ---------------------- ---------------------- ------------------
--        7566 03-DEC-81              14             14                   2                  2                  14.29                      1                  1 
--        7566 19-APR-87              14             14                   2                  2                  14.29                      1                  1 
--        7698 20-FEB-81              14             14                   5                  5                  35.71                      1                  1 
--        7698 22-FEB-81              14             14                   5                  5                  35.71                      1                  1 
--        7698 08-SEP-81              14             14                   5                  5                  35.71                      1                  1 
--        7698 28-SEP-81              14             14                   5                  5                  35.71                      1                  1 
--        7698 03-DEC-81              14             14                   5                  5                  35.71                      1                  1 
--        7782 23-JAN-82              14             14                   1                  1                   7.14                      1                  1 
--        7788 23-MAY-87              14             14                   1                  1                   7.14                      1                  1 
--        7839 02-APR-81              14             14                   3                  3                  21.43                      1                  1 
--        7839 01-MAY-81              14             14                   3                  3                  21.43                      1                  1 
--        7839 09-JUN-81              14             14                   3                  3                  21.43                      1                  1 
--        7902 17-DEC-80              14             14                   1                  1                   7.14                      1                  1 
--             17-NOV-81              14             14                   1                  1                   7.14                      1                  1