Re: Query to get the min of the total

Hiya Oliveiros,

This looks much better now, although the results is not good yet :)

By using the nested query this is what I have now:

SELECT petname,min(total)
FROM(
SELECT petname,SUM(quantity) as total
FROM pets NATURAL JOIN sales
GROUP BY petname) AS subquery
GROUP BY subquery.petname

As a result, it gives me all the results: all petnames with the sum of
quantity.

If I change min(total) for max(total) or for sum(total), the result is the
same, all petnames :(

Thanks a lot!

On 4 May 2012 11:05, Oliveiros d'Azevedo Cristina <
oliveiros(dot)cristina(at)marktest(dot)pt> wrote:

> **
> Howdy,
> Marta,
>
> have you tried a nested query?
> Something like
>
> SELECT petname, MIN(total)
> FROM (
> SELECT petname,SUM(quantity) as total
> FROM pets a
> NATURAL JOIN sales b
> GROUP BY petname
> ) x
>
>
> Best,
> Oliveiros
>
> ----- Original Message -----
> *From:* Marta Pérez Romero <martape(at)gmail(dot)com>
> *To:* pgsql-novice(at)postgresql(dot)org
> *Sent:* Friday, May 04, 2012 10:43 AM
> *Subject:* [NOVICE] Query to get the min of the total
>
> Hi All,
>
> I am trying to do something but cannot find the way.
> Hopefully you have some suggestions :)
>
> I have two tables, as follows:
>
> pets: with these columns: petcode, petname, petprice, petfamily
> sales: with these columns: salescode, petcode, quantity
>
> The pets table contains all the pets in my pet shop, with the code of the
> pet, its name, its price and the family it belongs to.
>
> So for example:
>
> petcode | petname | petprice | petfamily
> ------------------------------------------------------------
> 1 | canary | 5.00 | birds
> 2 | retriever | 12.00 | dogs
> 3 | siamese | 28.00 | cats
> 4 | sparrow | 7.00 | birds
> 5 | poodle | 16.00 | dogs
>
> The sales table tracks all the sales in the shop, with the code of the
> sale, the code of the pet that was bought, and the quantity of this pet
> bought.
>
> For example:
>
> salescode | petcode | quantity
> ----------------------------------------------
> 1 | 1 | 2
> 2 | 4 | 5
> 3 | 5 | 1
> 4 | 2 | 3
> 5 | 5 | 8
> 6 | 1 | 4
>
> Now, I need to build a query that returns the least sold pet, in quantity.
> It will include the pet name and the quantity sold.
>
> So in the example above it will be:
>
> petname | quantity
> retriever | 3
>
> Now, the problem I have is that I can´t work with nested aggregations.
> First I need to get the total quantity by petcode (which will be the sum
> of quantity). And then get the MIN of the SUM of quantity, which I don´t
> know how to do.
>
> This is the query so far:
>
> SELECT
> pets.petname,
> sum(sales.quantity)
>
> FROM sales INNER JOIN pets ON sales.petcode= pets.petcode
>
> GROUP BY
> pets.petname
>
> HAVING SUM(sales.quantity)=(SELECT MIN(quantity) FROM sales)
>
> ORDER BY pets.petname;
>
> But it does not return any result. How can I do it?
>
> Thanks very much
>
>