Thursday, February 1, 2024

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.