Re: Query to get the min of the total

Just a question related to this... what if there were two different pets
with the same total quantity sold, both being the least sold?
As we are saying LIMIT 1, only one would be displayed... how can I do so
both (or 3, or 4) are displayed?

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

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