SQLTeam.com | Weblogs | Forums

Add a Summary header to SQL Query Result

sql2012

#1

I have a select query that returns some financial data. I am trying to add a summary row before the results of my select query.

For example, my select query contains the following: date, amount, type (credit/debit). I need to return count, total debit, total credit, as the header (first row), followed by the select query result.

5               | 1000       | 1500
6/1/17          | 500.00     | Credit
6/2/17          | 500.00     | Debit
6/4/17          | 500.00     | Debit
6/6/17          | 500.00     | Credit
6/11/17         | 500.00     | Credit

I am not sure how to go about this.


#2

It always seems a bit "excessive" to me, but I tend to get all the detail-data into a #TEMP table, then add a row for the Summary, and then display the data (inclusive of the summary row)

If I need to control the order (Summary FIRST or Summary LAST) I add a column (to #TEMP) for "Sequence" or "Section" and put, say, 2 in that for all the details rows, and 1 for the Summary (at top) and then ORDER BY that column.

I suppose an alternative would be

SELECT *
FROM
(
SELECT [Section]=2, Col1, Col2, ..., Value1, Value2
FROM #TEMP
UNION ALL
SELECT [Section]=1, 'Total', '----', ..., SUM(Value1), SUM(Value2)
FROM #TEMP
) AS T
ORDER BY [Section], Col1, ...

#3

You're not going to be able to display an integer count and a date in the same column, as in the first column. Similarly, won't be able to mix numeric total and char data in the same column, as in the third column.

Best might be a separate query and simply display the results together in the output, although they come from different queries.


#4

True. If you you can modify the way you present the results you can use the GROUP BY WITH ROLLUP to create a summary and sort so it's at the top.

DECLARE @Table TABLE
(
SomeDate DATE NOT NULL,
SomeValue MONEY NOT NULL,
CRDB VARCHAR(6) NOT NULL CHECK (CRDB IN ('Credit','Debit'))
);
INSERT INTO @Table
SELECT * FROM
(
VALUES
('2017-06-01', 500, 'Credit'),
('2017-06-02', -500, 'Debit'),
('2017-06-04', -500, 'Debit'),
('2017-06-06', 500, 'Credit'),
('2017-06-11', 500, 'Credit')
) As d (SomeDate, SomeValue, CRDB);

SELECT COUNT(*) As TotalItems, SUM(SomeValue) As TotalValue, ISNULL(CRDB, 'Total') As Section
FROM @Table
GROUP BY CRDB WITH ROLLUP
ORDER BY Section DESC