Click here to Skip to main content
15,917,971 members
Please Sign up or sign in to vote.
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
Updated 5-Jan-20 6:38am

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[^]
 
Share this answer
 
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 :)
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!
 
Share this answer
 
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900