December 10, 2011 at 12:45 pm
--P8.8
Write the query that will show (for all the invoices) the invoice number, the invoice amount, the average invoice amount,
and the difference betwee the average invoice amount and the actual
invoice amount.
USE CH08_SimpleCo;
SELECT INVOICE.INV_NUM, INVOICE.INV_AMOUNT,
(SELECT AVG(INVOICE.INV_AMOUNT)
FROM INVOICE) AS [AVERAGE INVOICE AMOUNT],
(INV_AMOUNT - (SELECT AVG(INV_AMOUNT)
FROM INVOICE)) AS DIFF
FROM INVOICE
GROUP BY INVOICE.INV_NUM, INVOICE.INV_AMOUNT;
This is the answer to what I have but I still don't exactly understand the why to this query. Why was the SELECT subquery setup this way? I suppose that the reason is because I still dont' understand much about Subquery. I've read a number of documents but apparently it hasn't been dumbed down to my level. Anybody care to comment on their understanding of subqueries and when to Use the subquery? đ
Thanks!
December 11, 2011 at 4:20 am
Well, first of all I start off with formatting the code in a way so that it shows a bit clearer what it does:
SELECT
INVOICE.INV_NUM,
INVOICE.INV_AMOUNT,
(
SELECT
AVG(INVOICE.INV_AMOUNT)
FROM
INVOICE
) AS [AVERAGE INVOICE AMOUNT],
(
INV_AMOUNT - (
SELECT
AVG(INV_AMOUNT)
FROM
INVOICE
)
) AS DIFF
FROM
INVOICE
GROUP BY
INVOICE.INV_NUM,
INVOICE.INV_AMOUNT;
As one can see, the following code
SELECT
AVG(INV_AMOUNT)
FROM
INVOICE
Returns nothing more than a scalar (a single numeric value). As such, you can look at it that way too. What the code does in pseudo code is actually
SELECT
INV_NUM,
INV_AMOUNT,
Average(INV_AMOUNT),
INV_AMOUNT - Average(INV_Amount)
FROM
INVOICE
I hope this makes it a bit more clear.
December 12, 2011 at 5:16 am
Yeah I'm looking into this right now. I do appreciate your response. I've looked up a couple of threads on this already in the forums but none so far that provides the information that Im looking for. I'll get back on this asap.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply