-
Please help SQL noob with query!
Hi all i have a problem with a simple query im hoping one of you clever sql junkies can help me
ok i have a product table and am listing product id with product cost like so:
SELECT x.id,x.cost
FROM product x
Now the problem occurs when i want to add another column which lists tha average cost of items. I have tried this:
SELECT x.id,x.cost,AVG(x.cost)
FROM product x
GROUP BY x.id,x.cost;
I have also tried union. But to no avail :( i presume i am using totally the wrong technique? please point me in the right direction! thx muchly
Last edited by sambucaboy; 03-23-2005 at 12:07 PM.
-
Re: Please help SQL noob with query!
In this example the cost for each row would be equal to the average cost.
You can not average one number against itself.
Code:
SELECT x.id,x.cost,AVG(x.cost)
FROM cost x
GROUP BY x.id,x.cost;
If you had a detail table that showed what you sold the item for over a period of time, then you could average the cost and get a meaningful number. Using an outer join and an NVL to account for new items that have not been sold yet.
Code:
SELECT x.id, NVL(od.avg_cost, 0) avg_cost
FROM cost x
LEFT OUTER JOIN
( SELECT product_id, AVG(cost) avg_cost
FROM order_detail
WHERE sale_date BETWEEN TO_DATE('01-JAN-2004') AND
TO_DATE('01-JAN-2005')
GROUP BY product_id ) od
ON x.id = od.product_id
GROUP BY x.id;
-
thanks for that gandolf that was very useful im getting better at this by the day
I think i didnt make it clear exactly what i wanted though. What i wanted was the third column to be the average cost of all items in the cost table. Therefore i could compare the cost of a particular item against the average cost of other items if you get me? Is there a way of doing this without creating another table?
i.e the output i want:
id cost av cost
a1 10 20
a2 20 20
a3 30 20
many thanks!...sb
-
Re: Re: Please help SQL noob with query!
This should work.
Code:
SELECT x.id, x.cost, ( SELECT AVG(cost) cost
FROM cost ) avg_cost
FROM cost x
-
Code:
select
p.id, p.cost, foo.avg_cost
from
product p,
(select avg(cost) avg_cost from product) foo;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Code:
SELECT id, cost, AVG (cost) OVER () avg_cost
FROM product;
-
Sweet! thanks a lot guys that was exactly what I needed to know. Very simple but could not find it in any of my books!
Cheers....sb
-
Originally posted by jmodic
Code:
select
p.id, p.cost, foo.avg_cost
from
product p,
(select avg(cost) avg_cost from product) foo;
Hey that's a cartesian product!!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|