Sql min/sum statement problem

Question

0.00/5 (No votes)

See more:

i want to return the minimum value of Sum(units_sold) in this code

SELECT dim_product.product_name, Sum(fact_sales.units_sold) AS 
SumOfunits_sold
FROM dim_product 
INNER JOIN fact_sales 
ON dim_product.product_code = fact_sales.product_code
GROUP BY dim_product.product_name
ORDER BY Sum(fact_sales.units_sold) DESC;

What I have tried:

i try some way but i cant do that
thanks for your help

Posted 5-Jan-20 5:11am

Member 14707451

Updated 5-Jan-20 6:38am

Add a Solution

2 solutions

Solution 2

What about using your query as as subquery?

SQL

SELECT MIN(T.SumOfunits_sold) AS MinSumOfUnits
FROM (
    SELECT dp.product_name, Sum(fs.units_sold) AS SumOfunits_sold
    FROM dim_product AS dp
    INNER JOIN fact_sales AS fs ON dp.product_code = fs.product_code
    GROUP BY dp.product_name
) AS T;

More:
SQL subqueries (Microsoft Access SQL) | Microsoft Docs[^]
Microsoft Access tips: Subquery basics[^]

Good luck!

Posted 5-Jan-20 6:39am

Maciej Los

Comments

Member 14707451 5-Jan-20 12:44pm

thank you so much for your help , its work for me correctly , thank u sir for this help
but can i get maximum and minimum at the same time?

Member 14707451 5-Jan-20 14:18pm

or how can i elect product_name in the same code?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2020-01-05T05:41:00

Solution 1

If I understand the question correctly, you could use TOP N for that. Something like

SQL

SELECT TOP 1
       dim_product.product_name, 
       Sum(fact_sales.units_sold) AS SumOfunits_sold
FROM dim_product 
INNER JOIN fact_sales ON dim_product.product_code = fact_sales.product_code
GROUP BY dim_product.product_name
ORDER BY Sum(fact_sales.units_sold) ASC;

For more information on TOP N, see ALL, DISTINCT, DISTINCTROW, TOP Predicates - Access[^]

Posted 5-Jan-20 5:41am

Wendelius

Comments

Maciej Los 5-Jan-20 12:32pm

5ed!

Wendelius 6-Jan-20 2:44am

Thank you!

Member 14707451 6-Jan-20 7:29am

thank u so much for your help

Wendelius 6-Jan-20 8:31am

You're very welcome :)