Skip site navigation (1) Skip section navigation (2)

Re: Query to get the min of the total

From: Marta Pérez Romero <martape(at)gmail(dot)com>
To: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Query to get the min of the total
Date: 2012-05-04 10:34:19
Message-ID: CACnbkr=7uYB8nUtU0AaoVmooqseb1TxawM=AP+-5O8Wy7v7dWQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>>
>>
>

In response to

Responses

pgsql-novice by date

Next:From: Marta Pérez RomeroDate: 2012-05-04 10:39:51
Subject: Re: Query to get the min of the total
Previous:From: Marta Pérez RomeroDate: 2012-05-04 10:23:25
Subject: Re: Query to get the min of the total

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group