Why AVG() give no result?

Question

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(...)" ?

dont you need to `group by` weigth as well or at least use a aggregation like `AVG()` in the clolumns? — Nebi, Sep 23 '16 at 11:35
The order was just to display the name and the weigth of dogs, that's why if I do something like that, I disobey. But, on test purpose, I add a AVG() in the select (without group by / having) and then I get just one row (the first dog found and the avg of all of them). — Zac, Sep 23 '16 at 19:40

captainsac · Answer 1 · 2016-09-23T11:41:44.803

2

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

edited Sep 23 '16 at 11:41

answered Sep 23 '16 at 11:33

captainsac

2,484
3
27
48

Thanks, I did the first version that you replied and it works, but I was wondering why the second one, that not required to do twice the 'select libTpAni' wasn't working with avg() but if I put a number (30 is the result of the average function), I get what i wanted too. I didn't know that we can declare a variable or how to use them, so thanks a lot for that ! – Zac Sep 23 '16 at 19:43
1

Let me explain, DO one thing, Include `AVG(weigth)` in your select query and execute the query till Group By. Do not include line containg `Having` clause. The result you will get is nameAni,weigth and Avg_weigth. Now suppose you have a dog whose nameAni is "ABC" having weight 20. The result you will get is nameAni=ABC, weight = 20 and also as group by name is used it will Average the weight of only "ABC" dog. Hence Avg(Weight) will be again 20. so your condition Avg(weight) – captainsac Sep 26 '16 at 06:25
Hence calculate Average if the weight first and store it in a variable, then use this variable at the place of "30" that you are using. – captainsac Sep 26 '16 at 06:26

score 0 · Answer 2 · answered Sep 23 '16 at 13:08

0

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.

answered Sep 23 '16 at 13:08

Outshined

709
7
22

I will try it tomorrow, it seems to be what I searched to do with my groupby/having. – Zac Sep 23 '16 at 19:47

Why AVG() give no result?

2 Answers2