SQL Server 2008 Reporting: Sum of Max of group

Question

I have a table like this in the report designer:

Category:       1     2       3      4   Total
Max Amount:     x     y       z      c     ?

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

The max amount row is an expression that takes the max of each category. The source data has repeated values, so I just take the max. For example:

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

Other columns in the table are different, but amount will be the same so I cannot only select distinct 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 a 1000 categories. Then a dynamic pivot would be the best solution. 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 pivot

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