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 ;
  • The syntax is wrong for mysql - the tag removed. – Akina Jan 6 at 21:15
  • Please consider reading this advice – mustaccio Jan 6 at 21:32
  • 2
    SQL Server and MS Access are 2 completely different products. Please tag properly. – Eric Jan 6 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! :-) – Vérace Jan 7 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 at 10:32
-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
| improve this answer | |
  • you need to wrap the SELECT TOP 1 ... ORDER BY ... in a derived query for the UNION to work – Squirrel Jan 7 at 1:49
  • thank u so much for your help – sandra Jan 7 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.