How to Calculate Average of a Column and Then Include It in a Select Query in Oracle

How to calculate average of a column and then include it in a select query in oracle?

Your group by is what aggregates your average, and it is grouping by the whole table (I am assuming you did this to allow the select for everything) Just move your avg into another subquery, remove the overarching group by and that should solve it.

SELECT id, m_name AS "Mobile Name", cost AS Price,
    (SELECT AVG(cost) FROM mobile) AS Average,
    cost-(SELECT AVG(cost) FROM mobile) AS Difference 
FROM mobile;

When you run the basic SELECT AVG(cost) statement it is naturally grouping by the column specified (cost in this case) as that is what you are requesting. I would suggest reading up more on GROUP BY and aggregates to get a better grasp on the concept. That should help you more than just a simple solution.

UPDATE:

The answer below is actually from David's answer. It makes use the analytical functions. Basically, what is happening is that on each AVG call, you are telling the engine what to use for the function (in this case, nothing). A decent writeup on analytical functions can be found here and here and more with a google on the matter.

SELECT id, m_name AS "Mobile Name" cost AS Price, AVG(cost) OVER( ) AS Average, 
    cost - AVG(cost) OVER ( ) AS Difference
    FROM mobile

However, if your SQL engine allows for variables, you could just as easily do the below answer. I actually prefer this for future maintainability/readability. The reason is that a variable with a good name can be very descriptive to future readers of the code, versus an analytical function that does require a little bit more work to read (especially if you do not understand the over function).

Also, this solution duplicates the same query twice, so it might be worth storing your average in a SQL variable. Then you ca change your statement to simply use that global average

This is variables in SQL-Server (you will have to adapt it for your own instance of SQL)

DECLARE @my_avg INT;
SELECT @my_avg = AVG(cost) FROM Mobile;

    SELECT id, m_name AS "Mobile Name", cost AS Price,
        @my_avg AS Average, cost-@my_avg AS Difference 
    FROM mobile;

This solution will read a lot cleaner to future readers of your SQL, too

How to select average from row result on oracle?

Simple as this:

SELECT A.USERNAME
     , A.TOTAL
     , AVG(A.TOTAL) OVER () AS avg_total
     , A.MATH
     , AVG(A.MATH) OVER () AS avg_math
  FROM (your derived table) A
 WHERE A.TOTAL > 20
;

These are window functions.

Feel free to remove any rows you wish from the derived table or after the derived table in the outer WHERE clause (which you can add).

Average Date column in a query using Oracle SQL Developer v19

Indeed a window function function is probably the what you are after, as Gordon Linoff points out. Using standard joins is also a must-have.

I would, however, recommend also fixing the date arithmetic; not all years have 365 days, so your query is off on leap years - and inaccuracy increases when a person gets older. Instead, I would recommend months_between():

select p.first_name, p.last_name, p.sex, p.ethnicity, 
    round(months_between(sysdate, p.d_o_b) / 12, 0) as age,
    round(avg(months_between(sysdate, p.d_o_b) / 12) over(), 0) as avg_age
from patient p 
inner join tests t on t.patient_id = p.nhs_number
where t.result = 'positive' and t.date_of_test + 14 > sysdate
group by p.first_name, p.last_name, p.sex, p.ethnicity, p.d_o_b
order by age desc;

How can I calculate the average of the sum of 2 columns in ORACLE?

Just like you said:

select avg(sal + nvl(comm, 0)) avg_sal_comm
from emp

If commission part is expressed as percentage, then

select avg(sal + sal * nvl(comm, 0) / 100) avg_sal_comm
from emp;

NVL is here because not all employees have comm column value.

oracle, select query with average and sum

select   category, count(*) as categ_count, avg(price) as avg_price
from     table
group by category;

Don't use column names in double-quotes, that is a very poor practice in Oracle. Just make sure your column names follow the rules (only alphanumeric characters and underscore, begin with a letter, and don't use Oracle keywords like DATE or TYPE).

Finding rows which match the average value of a column

Your query can be boiled down to:

SELECT c.gridx, c.gridy, COUNT(*) AS requests_cnt
FROM requests r 
JOIN clients c ON c.id = r.client_id 
GROUP BY c.gridx, c.gridy;

i.e. get the request count per gridx/gridy. You want to determine the average count and then only show gridx/gridy pairs that occur exactly that often. In your example this would be the pairs (2|5), (-1|-3), but most often, I guess, that would be no pairs at all.

The easiest approach seems to be to get the average on-the-fly by applying AVG OVER:

SELECT gridx, gridy
FROM
(
  SELECT 
    c.gridx, c.gridy, COUNT(*) AS requests_cnt,
    AVG(COUNT(*)) OVER () AS avg_requests_cnt
  FROM requests r 
  JOIN clients c ON c.id = r.client_id 
  GROUP BY c.gridx, c.gridy
) pairs
WHERE requests_cnt = avg_requests_cnt
ORDER BY gridx, gridy;

sql computing average with a group by?

from managers_and_salaries
where manager_name in (select distinct manager_name
                       from managers_and_salaries)

That where clause is redundant: The from clause already ensures that only managers from managers_and_salaries are considered.

Once we drop that clause, managers_and_salaries is only used once, and it may be simpler to inline its definition:

select m.manager_name, avg(w.salary)
from manages m 
inner join works w on m.employee_name = w.employee_name
group by manager_name;

---

Related Topics