Click here to Skip to main content
14,427,367 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 5-Jan-20 7:38am
Rate this:
Please Sign up or sign in to vote.

Solution 1

If I understand the question correctly, you could use TOP N for that. Something like
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[^]
   
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 :)
Rate this:
Please Sign up or sign in to vote.

Solution 2

What about using your query as as subquery?
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!
   
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?

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100