Calculate GroupBy Max count and display as new column in SQL Server

elvisidrizi1
 
on Aug 14, 2020 05:51 AM
708 Views

Hello Everyone,

I have this code and I want to pass the result of the total from LESH to another column Here is the code

Thank you for your reply,  Here is some example:

Total

Year

Department

Lesh

38.04

2020-01

Grocery

1

269.24

2020-03

Grocery

2

611.95

2020-04

Grocery

7

665.81

2020-05

Grocery

11

388.88

2020-06

Grocery

5

412.76

2020-07

Grocery

14

Total

 

 

14

This is how I get the results, and what I want is like this 

Total

Year

Department

Lesh

38.04

2020-01

Grocery

14

269.24

2020-03

Grocery

 

611.95

2020-04

Grocery

 

665.81

2020-05

Grocery

 

388.88

2020-06

Grocery

 

412.76

2020-07

Grocery

 

;WITH CTE AS
(
    SELECT
        CAST(SUM(Price) AS VARCHAR(20)) As Total,
        CONVERT(char(7), date, 120) As [Year],
        Department
        ,COUNT(ISNULL(CONVERT(INT, CONVERT(VARCHAR(MAX),quantity)),0)) as Lesh
    FROM dbo.Expenses
    WHERE
    CONVERT(char(7), date, 120) Between '2020-01' And '2020-07' AND Department = 'FixCost'
    GROUP BY CONVERT(char(7), date, 120), Department
)
  
SELECT * FROM CTE
UNION ALL
SELECT 'Total','','',Total FROM
(
    SELECT TOP 1 COUNT(CONVERT(char(7), date, 120)) Total
    FROM dbo.Expenses
    WHERE CONVERT(char(7), date, 120) Between '2020-01' And '2020-07' AND Department = 'FixCost'
    GROUP BY CONVERT(char(7), date, 120)
    ORDER BY COUNT(CONVERT(char(7), date, 120)) DESC
)t

Thank you for your patience, Elvis

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 14, 2020 06:57 AM

Hi elvisidrizi1,

You can't display the value in first row only. The value will be displayed in all the rows. 

SQL

SELECT
    CAST(SUM(Price) AS VARCHAR(20)) As Total,
    CONVERT(char(7), date, 120) As [Year],
    Department,
    (SELECT TOP 1 COUNT(CONVERT(char(7), date, 120)) 
	FROM dbo.Expenses
	WHERE CONVERT(char(7), date, 120) Between '2020-01' And '2020-07' AND Department = 'Grocery'
	GROUP BY CONVERT(char(7), date, 120)
	ORDER BY COUNT(CONVERT(char(7), date, 120)) DESC) Lesh
FROM dbo.Expenses
WHERE
CONVERT(char(7), date, 120) Between '2020-01' And '2020-07' AND Department = 'Grocery'
GROUP BY CONVERT(char(7), date, 120), Department

Output

Total Year Department Lesh
38.04 2020-01 Grocery 14
269.24 2020-03 Grocery 14
611.95 2020-04 Grocery 14
665.81 2020-05 Grocery 14
388.88 2020-06 Grocery 14
412.76 2020-07 Grocery 14