CODEWITHSUNDEEP
sqlpostgresqlpsql
Rothmanberger
Rothmanberger

Reputation: 175

Why is HAVING col > AVG(col) returning no rows?

This is a view showing each robber, their number of years in prison, the number of robberies committed and their total earnings (21 rows).

ActiveRobbers2 View

I then am trying to create another view to further manipulate the data in the view above. I want my new view to show only the nicknames of robbers whose NoYears (served in prison) = 0 and whose NoRobberies is greater than the average number of robberies.

CREATE VIEW ActiveRobbers3
AS
SELECT Nickname
FROM ActiveRobbers2
WHERE NoYears = 0
GROUP BY Nickname, NoRobberies
HAVING NoRobberies > AVG(NoRobberies);

This however returns no rows when I attempt to SELECT * FROM ActiveRobbers3. Can anyone see my problem? Cheers

Upvotes: 0

Views: 83

Answers (2)

seahawk
seahawk

Reputation: 1912

Your question is vague. Average of NoRobberies across all data of the table or data pertaining specific "Nickname".

Case 1: Average of NoRobberies across all data

CREATE VIEW ActiveRobbers3
AS
SELECT Nickname
FROM ActiveRobbers2
WHERE NoYears = 0
AND NoRobberies > (select AVG(NoRobberies) from ActiveRobbers2);

Case 2: Average of NoRobberies for respective nickname "Nickname"

CREATE VIEW ActiveRobbers3
AS
SELECT Nickname,NoRobberies

FROM ActiveRobbers2
WHERE NoYears = 0
GROUP BY Nickname
Having NoRobberies > AVG(NoRobberies);

Note: case-2 will give data only if a nickname has atleast two rows with different NoRobberies. Sample code is for MySQL

Upvotes: 3

Benvorth
Benvorth

Reputation: 7722

Perhaps this works:

CREATE VIEW ActiveRobbers3
AS
SELECT Nickname
FROM ActiveRobbers2, 
(SELECT AVG(AVG_NoRobberies) as AVG_NoRobberies FROM ActiveRobbers2) as b
WHERE NoYears = 0
AND NoRobberies > b.AVG_NoRobberies

Upvotes: 0

Related Questions