SELECT id_film, sq1.counter
FROM(
SELECT id_film, COUNT(*) as counter
FROM film_vorstellung
GROUP BY id_film
) as sq1 WHERE sq1.id_film = id_film
The query returns:
id_film | counter |
---|---|
3 | 1 |
5 | 1 |
4 | 1 |
6 | 2 |
2 | 3 |
1 | 1 |
so far, so good.
Now i want to get every id_film which counter is higher than the average counter value.. i am trying to do that for hours now. The most common syntax error is that i cant use the average method where i tried to...
CodePudding user response:
You can do this with a CTE and a scalar query expression. Almost in plain language, first calculate the counter values per id_film
and then 'get every id_film whose counter is higher than the average counter value'.
with cntrs as -- your subquery
(
SELECT id_film, COUNT(*) as cntr
FROM film_vorstellung
GROUP BY id_film
)
select id_film, cntr
from cntrs
where cntr > (select avg(cntr) from cntrs);
CodePudding user response:
if you don't want to use with , an alternative solution might be like this
select y.id_film,y.cnt
from film_vorstellung x
JOIN
( select id_film,COUNT(id_film) as cnt ,
CAST( (SELECT DISTINCT SUM(Count(g.id_film)) OVER () as SumCount FROM film_vorstellung g GROUP BY g.id_film) as DECIMAL) /
CAST((SELECT count(DISTINCT id_film) FROM film_vorstellung) AS DECIMAL ) avgs
from film_vorstellung
group by id_film
) y
on x.id_film=y.id_film
where y.cnt >y.avgs
group by y.id_film,y.cnt
or
SELECT id_film, sq1.counter
FROM(
SELECT id_film, COUNT(*) as counter
FROM film_vorstellung
GROUP BY id_film
) as sq1 WHERE sq1.id_film = id_film
group by id_film,sq1.counter
having AVG (sq1.counter)> (SELECT AVG(X.counter) FROM ( SELECT id_film, COUNT(*) as counter FROM film_vorstellung group by id_film )X )
go