How to find the Minimum and Maximum value and Count of items from a Table in SQL Server

How to find the Minimum and Maximum value and Count of items for Min and Max values from a Table in SQL Server
Scenario:

Suppose we have a sample Products table with Prices details.

/* Create a sample table */

CREATE TABLE [dbo].[tbl_Products](

    [ProdID] [int] NOT NULL,

  [ProdName] [varchar](255) NOT NULL,

  [Price] [int] NOT NULL

) ON [PRIMARY]

GO

/* Insert sample records */

INSERT INTO [dbo].[tbl_Products] ([ProdID], [ProdName], [Price])

VALUES (121, 'A',25), (122,'B',35), (123,'C',10),(124,'D',35),

(125,'E',10), (126,'F',25),(127,'G',35),(128,'H',10),(129,'I',20),(130,'J',10)

Select * From [dbo].[tbl_Products] ;

Now from the above table, we need to calculate the following items to fulfil the Scenario:

- Highest Price (35)

- Count of Products with Highest Price (3)

- Lowest Price (10)

- Count of Products with Lowest Price (4)

- Finally, we need to calculate the Variance: (3*35) - (4*10) = (105 - 40) = 65

We can achieve this Scenario with the help of the Common Table Expression (CTE) as per below:

WITH cte_ProdPriceMinMax

AS

(

SELECT DISTINCT

    MAX(P1.[Price]) OVER (ORDER BY P1.[Price] DESC) Max_Price,

    FIRST_VALUE(S1.[Prod_Count_by_Price])

            OVER(ORDER BY S1.[Prod_Price] DESC) Max_Price_Prod_Count,

    MIN(P1.[Price]) OVER(ORDER BY P1.[Price] ASC) Min_Price,

    FIRST_VALUE(S1.[Prod_Count_by_Price])

            OVER(ORDER BY S1.[Prod_Price] ASC) Min_Price_Prod_Count

FROM [dbo].[tbl_Products] P1

LEFT JOIN

  (

    SELECT DISTINCT

        MIN([Price]) OVER (PARTITION BY [Price]

                            ORDER BY [Price] ASC) Prod_Price,

        COUNT([ProdID]) OVER (PARTITION BY [Price]

                                ORDER BY [Price] ASC) Prod_Count_by_Price

        FROM [dbo].[tbl_Products]

  ) S1

ON  P1.[Price] = S1.[Prod_Price]

)

SELECT *,

    (Max_Price*Max_Price_Prod_Count) AS Max_Price_ProdVal ,

    (Min_Price*Min_Price_Prod_Count) AS Min_Price_ProdVal,

    (Max_Price*Max_Price_Prod_Count) - (Min_Price*Min_Price_Prod_Count)

        AS VarOfMaxMinVal

FROM cte_ProdPriceMinMax

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------