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

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

 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

on Aug 14, 2020 06:57 AM

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