Re: Query to get the min of the total

Yes! It works now, thanks a mill :):):)

On 4 May 2012 11:23, Marta Pérez Romero <martape(at)gmail(dot)com> wrote:

> 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
>>
>>
>