CODEWITHSUNDEEP
sqlaveragehaving
Zac
Zac

Reputation: 31

Why AVG() give no result?

I try to select only dog with weight superior to the average weight of all dogs.

I have this SQL query: 

SELECT nameAni, weigth
   FROM Animal
WHERE idTpAni IN (SELECT idTpAni
            FROM TypeAnimal
          WHERE libTpAni = 'DOG')
GROUP BY nameAni
HAVING AVG(weigth) < weigth;

If I tip 30 instead of AVG(weigth), I get the result I'm looking for.

Could you explain me why I have no result when typing "AVG(...)" ?

Upvotes: 3

Views: 92

Answers (2)

Outshined
Outshined

Reputation: 740

SELECT nameAni, weigth
   FROM Animal
WHERE idTpAni IN (SELECT idTpAni
                    FROM TypeAnimal
                    WHERE libTpAni = 'DOG') AND
        weight > AVG(weigth) over ()

Consider using partition instead of group by.

Upvotes: 0

captainsac
captainsac

Reputation: 2490

SELECT nameAni, weigth
FROM Animal
WHERE idTpAni IN (SELECT idTpAni
                  FROM   TypeAnimal
                  WHERE  Lower(libTpAni) = 'dog')
                  AND    weigth > (SELECT Avg(weigth)
                                   FROM   Animal
                                   WHERE  idTpAni IN (SELECT idTpAni
                                   FROM   TypeAnimal
                                   WHERE  Lower(libTpAni) = 'dog'))

If you want to go in a simple way, refer following

    DECLARE @weight FLOAT; --mind the type of this variable

    SELECT @weight=Avg(weigth)
    FROM   Animal
    WHERE  idTpAni IN (SELECT idTpAni
    FROM   TypeAnimal
    WHERE  Lower(libTpAni) = 'dog')

    SELECT nameAni, weigth
    FROM   Animal
    WHERE  idTpAni IN (SELECT idTpAni
    FROM   TypeAnimal
    WHERE  Lower(libTpAni) = 'dog')
    AND    weigth > @weight

Upvotes: 2

Related Questions