-1
select 
    substr(insert_date, 1, 14), 
    device, count(1) 
from 
    abc.xyztable   
where 
    insert_date >= DATE_SUB(NOW(), INTERVAL 10 DAY) 
group by 
    device, substr(insert_date, 1, 14) ;

and then I am trying to get average of the same rows count which I got above.

SELECT 
    date, device, AVG(count) 
FROM
    (SELECT 
         substr(insert_date, 1, 14) AS date,
         device,
         COUNT(1) AS count 
     FROM
         abc.xyztable
     WHERE
         insert_date >= DATE_SUB(NOW(), INTERVAL 10 DAY) 
     GROUP BY
         device, substr(insert_date, 1, 14)) a
 GROUP BY 
     device, date;

AS I found both queries return the same results, I tried for last 10 days data.

My purpose is to get the average rows count for last 10 days which I get from the above 1st query.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bilal shakir
  • 47
  • 7

2 Answers2

0

If you want an average you need to change the last GROUP BY

to get an average per device

GROUP BY device;

to get an average per date 

GROUP BY date;

or remove it completely to get an average for all rows in the sub-query

Update Below is a full example for getting the average per device

SELECT device, avg(count)
FROM (SELECT substr(insert_date,1,14) as date, device, count(1) as count 
      FROM abc.xyztable
      WHERE insert_date >=DATE_SUB(NOW(), INTERVAL 10 DAY) 
      GROUP BY device,substr(insert_date,1,14)) a
GROUP BY device;
Joakim Danielson
  • 43,251
  • 5
  • 22
  • 52
  • I am getting results, but those reults or same as 1st query. I am just confused how to get average count per device in last 10 days. – bilal shakir Jul 09 '18 at 09:58
  • @bilalshakir, I have update my answer with the full query. In my testing it doesn't return the same result as your first query. – Joakim Danielson Jul 09 '18 at 10:38
0

I'm not entirely sure what you're asking, the "difference" between the two queries is that the first one is valid but the second does not appear to be, as per HoneyBadger's comment. They also seem to be trying to achieve two different goals.

However, I think what you are trying to do is produce a query based on the data from the first query, which returns the date, device, and an average of the count column. If so, I believe the following query would calculate this:

WITH
   dataset AS (
       select substr(insert_date,1,14) AS theDate, device, count(*) AS 
       theCount 
       from abc.xyztable
       where insert_date >=DATE_SUB(NOW(), INTERVAL 10 DAY) 
       group by device,substr(insert_date,1,14)
       )

       SELECT theDate, device, (SELECT ROUND(AVG(CAST(theCount 
       AS FLOAT)), 2) FROM 
       dataset) AS Average
       FROM dataset
       GROUP BY theDate, device

I have referenced the accepted answers of this question to calculate the average: How to calculate average of a column and then include it in a select query in oracle? And this question to tidy up the query: Formatting Clear and readable SQL queries

Without having a sample of your data, or any proper context, I can't see how this would be especially useful, so if it was not what you were looking for, please edit your question and clarify exactly what you need.

EDIT: Based on what extra information you have provided, I've made a tweak to my solution to increase the precision of the average column. It now calculates the average to two decimal places. You have stated that this returns the same result as your original query, but the two queries are not formulating the same thing. If the count column is consistently the same number with little variation, the AVG function will round this, which in turn could produce results which look the same, especially if you only compare a small sample, so I have amended my answer to demonstrate this. Again, we'd all be able to help you much easier if you would provide more information, such as a sample of your data.

Barry Piccinni
  • 1,685
  • 12
  • 23