Обсуждение: Query to get the min of the total

Поиск
Список
Период
Сортировка

Query to get the min of the total

От
Marta Pérez Romero
Дата:
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

Re: Query to get the min of the total

От
"Oliveiros d'Azevedo Cristina"
Дата:
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 -----
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

Re: Query to get the min of the total

От
"Oliveiros d'Azevedo Cristina"
Дата:
Hello again, Marta,
 
Forget about the query I've just sent you.
It doesnt work.
 
Try this, instead
 
SELECT petname,SUM(quantity) as total
FROM pets a
NATURAL JOIN sales b
GROUP BY petname
ORDER BY total
LIMIT 1;
 
Best,
Oliveiros
----- Original Message -----
Sent: Friday, May 04, 2012 11:05 AM
Subject: Re: [NOVICE] Query to get the min of the total

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

Re: Query to get the min of the total

От
Marta Pérez Romero
Дата:
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.cristina@marktest.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 -----
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

Re: Query to get the min of the total

От
Marta Pérez Romero
Дата:
Yes! It works now, thanks a mill :):):)

On 4 May 2012 11:23, Marta Pérez Romero <martape@gmail.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.cristina@marktest.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 -----
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


Re: Query to get the min of the total

От
Marta Pérez Romero
Дата:
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@gmail.com> wrote:
Yes! It works now, thanks a mill :):):)


On 4 May 2012 11:23, Marta Pérez Romero <martape@gmail.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.cristina@marktest.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 -----
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



Re: Query to get the min of the total

От
"Oliveiros d'Azevedo Cristina"
Дата:
Trickier.
 
Ok, subqueries revisited.
 
Please try this out and tell me if it worked.
 
This is untested code, I might have made some mistake, including syntactic :-)
 
SELECT *
FROM
(
SELECT petname,SUM(quantity) as total
FROM pets a
NATURAL JOIN sales b
GROUP BY petname
) x
NATURAL JOIN
(SELECT MIN(total) as total
FROM
(
SELECT petname,SUM(quantity) as total
FROM pets a
NATURAL JOIN sales b
GROUP BY petname
)y
)z
 
Best,
Oliveiros
 
----- Original Message -----
Sent: Friday, May 04, 2012 11:39 AM
Subject: Re: [NOVICE] 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@gmail.com> wrote:
Yes! It works now, thanks a mill :):):)


On 4 May 2012 11:23, Marta Pérez Romero <martape@gmail.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.cristina@marktest.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 -----
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



Re: Query to get the min of the total

От
Marta Pérez Romero
Дата:
The below works!!! I am investigating if it covers everything, but looks good!
Thanks a mill!!!!

On 4 May 2012 12:12, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:
SELECT *
FROM
(
SELECT petname,SUM(quantity) as total
FROM pets a
NATURAL JOIN sales b
GROUP BY petname
) x
NATURAL JOIN
(SELECT MIN(total) as total
FROM
(
SELECT petname,SUM(quantity) as total
FROM pets a
NATURAL JOIN sales b
GROUP BY petname
)y
)z

Re: Query to get the min of the total

От
"Oliveiros d'Azevedo Cristina"
Дата:
Great to hear !!!
:-)
----- Original Message -----
Sent: Friday, May 04, 2012 12:19 PM
Subject: Re: [NOVICE] Query to get the min of the total

The below works!!! I am investigating if it covers everything, but looks good!
Thanks a mill!!!!

On 4 May 2012 12:12, Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt> wrote:
SELECT *
FROM
(
SELECT petname,SUM(quantity) as total
FROM pets a
NATURAL JOIN sales b
GROUP BY petname
) x
NATURAL JOIN
(SELECT MIN(total) as total
FROM
(
SELECT petname,SUM(quantity) as total
FROM pets a
NATURAL JOIN sales b
GROUP BY petname
)y
)z