-1

I have a table Saledetail with columns prodid, Quantity. I want to fetch the prodid with the minimum of sum of quantity from that table.

After going through some resource like: SQL query to select distinct row with minimum value

I wrote this query

select prodid, sum(quantity) as k
from saledetail
group by prodid

and its output is:

+--------+----+
| PRODID | K  |
+--------+----+
|    102 | 11 |
|    101 |  5 |
|    104 |  4 |
|    106 |  4 |
|    103 |  2 |
+--------+----+

Now in order to get prodid with the minimum value of k, I will have to use select min(k) from table_name. But I do not have a table_name for that query, since it is a sub-query. I also tried aliasing them, then I got error and couldn't figure out.

I would like to know how to get my requirement done?

In case any better solution that is also welcome.

Thank you

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
abhishek
  • 41
  • 7
  • *in order to get prodid having minimum value of k, i will have to use `select min(k) from table_name`* Simply sort by `sum` and take 1st record. – Akina Apr 10 '20 at 09:48
  • What is k in your out output and that is never the out put of your first query. Please read https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – nbk Apr 10 '20 at 09:51
  • Nope, it's not working in my compiler. It's showing 'ORA-00936: missing expression. – abhishek Apr 10 '20 at 09:56
  • 1
    @abhishek: If you get ORA-00936, then your DBMS is Oracle, not MySQL. These are two different database management systems. Which is your Oracle version? (Run `SELECT * FROM v$version;`). – Thorsten Kettner Apr 10 '20 at 09:58
  • Version 12.1.0.2.0. Thank you. since, i am a beginner in dbms , that's why i couldn't differentiate mysql and oracle. – abhishek Apr 10 '20 at 10:05
  • 1
    Okay, I've updated my answer to show the Oracle solution. (Which is also the solution in standard SQL, i.e. valid for all DBMS that comply with the standard in this regard.) – Thorsten Kettner Apr 10 '20 at 10:08
  • since, the sub-query is also a table. Is there any way to alias that with a name, so that it can be used like a table, As i tried to do? Can it be done in my case? Thank you. – abhishek Apr 10 '20 at 10:16
  • So far you have only shown a query without a subquery. But yes, a subquery is also a table and you can give it a name. In the following example I select all user last names in a subquery and call this subquery last_names: `select * from (select distinct last_name from users) last_names;`. – Thorsten Kettner Apr 10 '20 at 10:20
  • 1
    You can also use a with clause. In your example: `with sums as (select prodid, sum(quantity) as k from saledetail group by prodid) select * from sums where k = (select min(k) from sums);`, which is probably what you have been looking for. I have added this to my answer. – Thorsten Kettner Apr 10 '20 at 10:22
  • @nbk thank you for your concern, i got the same output as stated in my question, basically i aliased the attribute name. And i'm also a beginner so i didn't know about so much critical things, that had to be considered. – abhishek Jun 03 '20 at 04:47

1 Answers1

2

Oracle

To get the product(s) with the minimum total in Oracle, use FETCH FIRST ROWS:

select prodid, sum(quantity) as sum_quantity
from saledetail
group by prodid
order by sum(quantity)
fetch first rows with ties;

An alternative using your original query is:

with sums as 
(
  select prodid, sum(quantity) as sum_quantity 
  from saledetail 
  group by prodid
)
select * 
from sums
where sum_quantity = (select min(sum_quantity) from sums);

MySQL

This query gives you one product with the minimum quantity sum:

select prodid, sum(quantity) as sum_quantity
from saledetail
group by prodid
order by sum(quantity)
limit 1;

There is no ties clause for LIMIT, however. So, if two products or more share the same minimum total, above query would pick one of them arbitrarily.

Here is a simple query to get all products with the minimum quantity sum:

select prodid, sum(quantity) as sum_quantity
from saledetail
group by prodid
having sum(quantity) =
(
  select sum(quantity)
  from saledetail
  group by prodid
  order by sum(quantity)
  limit 1
);

As of MySQL 8 you can use a window function in order to read the table only once:

select prodid, sum_quantity
from
(
  select 
    prodid,
    sum(quantity) as sum_quantity,
    min(sum(quantity)) over () as min_sum_quantity
  from saledetail
  group by prodid
) aggregated
where sum_quantity = min_sum_quantity;

Both Oracle queries and the last MySQL query are standard SQL. MySQL doesn't support fetch first rows with ties, so the first Oracle query does not work in MySQL. The second using a WITH clause works as of MySQL 8.

Thorsten Kettner
  • 80,418
  • 7
  • 43
  • 64