CODEWITHSUNDEEP
sqlsql-server
Randy
Randy

Reputation: 1287

How do I GROUP BY ROLLUP without the second column when adding a summary row

I need to add a summary row to this sql query:

SELECT
    DATALENGTH([filedata]) AS [File Data Length]
   ,DATALENGTH([Compressed File Data]) AS [Compressed File Data Length]
   ,(CAST(DATALENGTH([Compressed File Data]) AS DECIMAL) / CAST(DATALENGTH([filedata]) AS DECIMAL)) AS [Reduced]
FROM [TEST].[dbo].[bmaitempic]
WHERE [filedata] IS NOT NULL

I want to show the average [Reduced] column in the summary.

Here is my query so far:

SELECT
    [File Data Length] = ISNULL([File Data Length], 0)
   ,[Compressed File Data Length]
   ,[Reduced] = AVG([Reduced])
FROM (SELECT
        DATALENGTH([filedata]) AS [File Data Length]
       ,DATALENGTH([Compressed File Data]) AS [Compressed File Data Length]
       ,(CAST(DATALENGTH([Compressed File Data]) AS DECIMAL) / CAST(DATALENGTH([filedata]) AS DECIMAL)) AS [Reduced]
    FROM [TEST].[dbo].[bmaitempic]
    WHERE [filedata] IS NOT NULL) AS t1
GROUP BY ROLLUP ([File Data Length], [Compressed File Data Length])

In the first query I get 153 rows. In the second query it returns 267 rows. How do I GROUP BY ROLLUP without the second column? I would also like to format [Reduced] to percent and change the 0 to text.

Any help would be appreciated.

Upvotes: 0

Views: 548

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Use GROUPING SETS:

GROUP BY GROUPING SETS (
    ([File Data Length], [Compressed File Data Length]),
    ()
)

The () says to summarize all the data, so it returns only one additional row, the overall summary.

Upvotes: 1

Related Questions