Untitled

SELECT id_samochodu
      ,marka
      ,model
      ,cena_katalogowa
      ,(select AVG(cena_katalogowa) from dbo.samochody where marka like 'opel') as srednia
      ,cena_katalogowa - (SELECT AVG(cena_katalogowa) FROM dbo.samochody WHERE marka LIKE 'opel') AS roznica
      ,CASE WHEN (SELECT AVG(cena_katalogowa) FROM dbo.samochody WHERE marka LIKE 'opel') - cena_katalogowa > 0 THEN 'NIE' ELSE 'TAK' END AS powyzej
      ,[nr_rejestracyjny]
  FROM dbo.samochody


select s1.*, s2.srednia, s1.cena_katalogowa - s2.srednia as roznica, case when (s1.cena_katalogowa - s2.srednia > 0) then 'TAK' else 'NIE' end as powyzej
from dbo.samochody s1 left join
    (select AVG(cena_katalogowa) as srednia, marka
    from dbo.samochody s2
    group by marka
    having marka like 'opel') s2
    on s1.marka = s2.marka
where s1.marka = 'opel'