How Can I Calculate a Table Row Summary Across Columns in SQL Server?

Computing Table Row Summary across Columns

Modern data analysis often requires building tables with summary statistics in the last row. Consider a table with rows showcasing various categories and their respective "Total Sales." You may want to add an extra row computing the total sales across all categories.

While unorthodox, here's how you can achieve this in SQL Server 2008 or beyond:

SELECT
  Type = ISNULL(Type, 'Total'),
  TotalSales = SUM(TotalSales)
FROM atable
GROUP BY ROLLUP(Type)
;

This assumes the Type column has no null values, and the rollup row (grand total) is denoted by the Total value. However, if null values are present, a more accurate representation using the GROUPING() function is:

SELECT
  Type = CASE GROUPING(Type) WHEN 1 THEN 'Total' ELSE Type END,
  TotalSales = SUM(TotalSales)
FROM atable
GROUP BY ROLLUP(Type)
;

Utilizing this approach, you can effectively add a summary row to your table, providing a quick and efficient way to calculate cross-column totals.

The above is the detailed content of How Can I Calculate a Table Row Summary Across Columns in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!