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

Re: Query to get the min of the total

From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: Marta Pérez Romero <martape(at)gmail(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Query to get the min of the total
Date: 2012-05-04 10:05:10
Message-ID: 90DB01C16D094EA3A6055DBE39B73EF5@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-novice
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 
  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: Oliveiros d'Azevedo CristinaDate: 2012-05-04 10:14:25
Subject: Re: Query to get the min of the total
Previous:From: Marta Pérez RomeroDate: 2012-05-04 09:43:12
Subject: Query to get the min of the total

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