0

So I am having this sql code:

select stone_number,stone_size,stone_shape,stone_weight 
from stone
left Join stone_price stp on stp.id_stone = stone.id_stone
group by stone_number,stone_size,stone_shape,stone_weight 
having  avg(stp.price) < stp.price;

Sqldeveloper returns: not a group by expression tho I still have group by in my code. My goal is to get stone items which price is higher then average price of all stones.

user3062657
  • 23
  • 1
  • 9
  • [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/a/55111083/3404097) – philipxy Jan 12 '23 at 06:09

2 Answers2

2

You can do that with a window function:

select *
from (
  select stone_number,stone_size,stone_shape,stone_weight, 
         stp.price, 
         avg(stp.price) over () as avg_price
  from stone
    left Join stone_price stp on stp.id_stone = stone.id_stone
)  
where price > avg_price;

Note that the condition on the outer joined table essentially turns your outer join into an inner join. If you also want to include rows that did not have a match in stone_price you need 

where price is null
   or price > avg_price;

Otherwise you can just change the left join to a "plain" join

Another option is a simple sub-select:

select stone_number,stone_size,stone_shape,stone_weight, 
from stone
  left Join stone_price stp on stp.id_stone = stone.id_stone
where stp.price > (select avg(price) from stone_price);
0

One approach is to use a stored procedure, You could set a variable like this:

DECLARE @average_price INT
SET @average_price = (SELECT Avg(stone_price) from stone

Then in your SELECT statement

select stone_number,stone_size,stone_shape,stone_weight 
from stone
left Join stone_price stp on stp.id_stone = stone.id_stone
WHERE stone_price > @average_price
logixologist
  • 3,694
  • 4
  • 28
  • 46
  • That is not valid for Oracle –  Apr 08 '16 at 21:54
  • That seems like a good idea, I never saw or done that only some basic SQL but I get the idea. I'm working in SqlDeveloper and I get error at @ when declaring the variable: Encountered the symbol "@" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior – user3062657 Apr 08 '16 at 21:56
  • Oh right... this is SQL for SQL Server. But that would be one approach to take in Oracle. Declare a variable, get the average and then use that in the where clause. – logixologist Apr 08 '16 at 21:58