0

hello everyone i try to find minimum and maximum value of SUM(units_sold) i test some ways but i can't find best solution please help me !

    SELECT TOP 1    dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
    FROM           dim_product dp
    INNER JOIN  fact_sales fs
        ON     dp.product_code = fs.product_code
    GROUP BY
            dp.product_name
    ORDER BY
        SUM(fe.units_sold) DESC
UNION ALL
     SELECT TOP 1     dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
      FROM   dim_product dp
      INNER JOIN  fact_sales fs
      ON     dp.product_code = fs.product_code
      GROUP BY
             dp.product_name
      ORDER BY
                SUM(fe.units_sold) ACS ;
7
  • The syntax is wrong for mysql - the tag removed.
    – Akina
    Jan 6, 2020 at 21:15
  • Please consider reading this advice
    – mustaccio
    Jan 6, 2020 at 21:32
  • 2
    SQL Server and MS Access are 2 completely different products. Please tag properly.
    – Eric
    Jan 6, 2020 at 23:12
  • There are a few articles on how to ask questions here on my profile - you might want to take a look? It's very important to specify the problem as precisely as possible because answers (very) frequently depend on the database server used - SQL is a standard, but it's not completely implemented by any RDBMS product and the variations are significant enough that you need to specify which one you are using. Also, some sample data and a desired result would be very helpful - perhaps in the form of a fiddle? p.s. welcome to the forum! :-) Jan 7, 2020 at 5:22
  • thanks all for your advice and help ... really i am new in 'stackexchange ' i don't know the tips and conditions Sorry :)
    – sandra
    Jan 7, 2020 at 10:32

1 Answer 1

-1

You could try this

WITH cte AS (
    SELECT dp.product_name, SUM(fs.units_sold) AS SumOfunits_sold
    FROM   dim_product dp
    INNER JOIN  fact_sales fs ON dp.product_code = fs.product_code
    GROUP BY dp.product_name
)
SELECT *
FROM (
    SELECT TOP 1 product_name, SumOfunits_sold 
    FROM cte 
    ORDER BY SumOfunits_sold DESC
) a
UNION
SELECT *
FROM (
    SELECT TOP 1 product_name, SumOfunits_sold 
    FROM cte 
    ORDER BY SumOfunits_sold
) b
2
  • you need to wrap the SELECT TOP 1 ... ORDER BY ... in a derived query for the UNION to work
    – Squirrel
    Jan 7, 2020 at 1:49
  • thank u so much for your help
    – sandra
    Jan 7, 2020 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.