SQL Server 2008 Reports: Max. Groups

Question

I have a table in the report designer:

Category: 1 2 3 4 Total Max Amount: xyzc ? 

I need to get the total amount of Max Amount, but the expressions will not allow me to take Sum (Max (amount)), and the total amount for this cell is disabled.

The maximum number of lines is an expression that takes the maximum of each category. The source data has duplicate values, so I just take max. For example:

 Category Amount 1 4.6 1 4.6 1 4.6 2 5 3 4 

The other columns in the table are different, but the number will be the same, so I can not only select individual values.

Answer 1

Maybe something like this:

 SELECT SUM(t1.maxAmout) FROM ( SELECT MAX(t.Amout) AS maxAmout, t.Category FROM yourTable AS t GROUP BY t.Category ) AS t1 

You can also do it like this. If you are using SQL Server 2005 +:

 SELECT pvt.[1], pvt.[2], pvt.[3], ( pvt.[1]+ pvt.[2]+ pvt.[3] ) AS Total FROM ( SELECT t.Category, t.Amout FROM yourTable AS t ) AS SourceTable PIVOT ( MAX(Amout) FOR Category IN([1],[2],[3]) ) AS pvt 

EDIT

If you have 1000 categories. Then the optimal solution would be a dynamic rod. So like this:

Test Data

 CREATE TABLE #T ( Category INT, Amout FLOAT ) INSERT INTO #T VALUES (1,4.6), (1,4.6), (1,4.6), (2,5), (3,4) 

Unique column names

 DECLARE @cols VARCHAR(MAX) DECLARE @colsTotal VARCHAR(MAX) ;WITH CTE AS ( SELECT ROW_NUMBER() OVER(PARTITION BY t.Category ORDER BY t.Amout) AS RowNbr, t.* FROM #T AS t ) SELECT @cols = COALESCE(@cols + ','+QUOTENAME(Category), QUOTENAME(Category)), @colsTotal=COALESCE(@colsTotal + '+ISNULL('+QUOTENAME(Category)+',0)', 'ISNULL('+QUOTENAME(Category)+',0)') FROM CTE WHERE CTE.RowNbr=1 ORDER BY Category 

Dynamic rod

 DECLARE @query NVARCHAR(4000)= N'SELECT ' +@cols +', ( ' +@colsTotal +' ) AS Total FROM ( SELECT t.Category, t.Amout FROM #T AS t ) AS SourceTable PIVOT ( MAX(Amout) FOR Category IN(' +@cols +') ) AS pvt' EXECUTE(@query) 

Answer 2

 WITH aggregate AS ( SELECT category, MAX(amount) AS max_amount FROM yourTable GROUP BY category ) SELECT MAX(CASE WHEN category = 1 THEN max_amount ELSE NULL END) AS [1], MAX(CASE WHEN category = 2 THEN max_amount ELSE NULL END) AS [2], MAX(CASE WHEN category = 3 THEN max_amount ELSE NULL END) AS [3], MAX(CASE WHEN category = 4 THEN max_amount ELSE NULL END) AS [4], SUM(max_amount) AS [total] FROM aggregate 

Answer 3

Next, a single value is returned:

 SELECT DISTINCT SUM(MAX(Amount)) OVER () FROM atable GROUP BY Category 

You can use this as a subquery calculating the value of the Total column.

Alternatively, TOP (1) can be used instead of DISTINCT (I don’t know why I could not think about this before):

 SELECT TOP (1) SUM(MAX(Amount)) OVER () FROM atable GROUP BY Category