StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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. </p> <pre><code>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; </code></pre> <p>When you run the basic <code>SELECT AVG(cost)</code> 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 <a href="http://www.w3schools.com/sql/sql_groupby.asp" rel="noreferrer">GROUP BY</a> and <a href="http://www.w3schools.com/sql/sql_functions.asp" rel="noreferrer">aggregates</a> to get a better grasp on the concept. That should help you more than just a simple solution.</p> <p><strong>UPDATE:</strong></p> <p>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 <a href="http://www.orafusion.com/art_anlytc.htm" rel="noreferrer">here</a> and <a href="http://www.orafaq.com/node/55" rel="noreferrer">here</a> and more with a google on the matter.</p> <pre><code>SELECT id, m_name AS "Mobile Name" cost AS Price, AVG(cost) OVER( ) AS Average, cost - AVG(cost) OVER ( ) AS Difference FROM mobile </code></pre> <p>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). </p> <p>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</p> <p>This is variables in SQL-Server (you will have to adapt it for your own instance of SQL)</p> <pre><code>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; </code></pre> <p>This solution will read a lot cleaner to future readers of your SQL, too</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload