How to display total at the bottom of a column

Transact-SQL
1

I have the following output:

Code Count
1 1
2 4
3 10

How do i display the total count of 15 at the end of the results column of Count? It should look like this:

Code Count
1 1
2 4
3 10
-------
15

Thank you.

2

select code, sum(count)
from table
group by rollup (code)

see here

3

Thank you, that will help.

4

I do it something like this:

SELECT 1 AS Section,
       Code,
       Count
FROM MyTable
WHERE ...
UNION ALL
SELECT 2 AS Section,
       NULL AS Code,
       SUM(Count) AS Count
FROM MyTable
WHERE ...
ORDER BY Section, Code
5

Hi Kristen,
I tried your example but gives me error:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Here is my query:

SELECT t1.Code, t1.Count, t1.Percentage, t2.Code, t2.Count,t2.Percentage
FROM
(SELECT SUBSTRING(a.Code,1,5) Code
, COUNT(SUBSTRING(a.Code,1,5)) AS Count
,CONVERT(VARCHAR,CAST(ROUND(((cast(count(SUBSTRING(a.Code,1,5)) as decimal(10,4)) / t.total) * 100),4)AS decimal(10,4)) ) + '%' as Percentage
,ROW_NUMBER() OVER (ORDER BY SUBSTRING(a.Code,1,5)) 'RowNumber'
FROM TableA AS a
INNER JOIN (SELECT COUNT() AS total FROM TableA) AS t ON (1=1)
GROUP BY SUBSTRING(a.Code,1,5), t.total
UNION ALL
SELECT '' AS Code,
SUM(COUNT(SUBSTRING(ac.Code,1,5))) AS Count, '' AS Percentage, '' AS RowNumber
FROM TableA AS ac
INNER JOIN (SELECT COUNT() AS total FROM TableA) AS tc ON (1=1)
GROUP BY SUBSTRING(ac.Code,1,5), tc.total
) AS t1
FULL OUTER JOIN
(SELECT SUBSTRING(b.Code,1,5) Code
, COUNT(SUBSTRING(b.Code,1,5)) AS Count
,CONVERT(VARCHAR,CAST(ROUND(((cast(count(SUBSTRING(b.Code,1,5)) as decimal(10,4)) / t.total) * 100),4)AS decimal(10,4)) ) + '%' as Percentage
,ROW_NUMBER() OVER (ORDER BY SUBSTRING(b.Code,1,5)) 'RowNumber'
FROM TableB AS b
INNER JOIN (SELECT COUNT() AS total FROM TableB) AS t ON (1=1)
GROUP BY SUBSTRING(b.Code,1,5), t.total
UNION ALL
SELECT '' AS Code,
SUM(COUNT(SUBSTRING(ac.Code,1,5))) AS Count, '' AS Percentage, '' AS RowNumber
FROM TableB AS ac
INNER JOIN (SELECT COUNT() AS total FROM TableB) AS tc ON (1=1)
GROUP BY SUBSTRING(ac.Code,1,5), tc.total
) AS t2

6

Hiya!

Have you tried CUBE? Cube the columns you are grouping by.

7

Sorry! It was the first answer. Rollup is great too!

8

I do not know how to do cube. Thanks.

9

Sounds like you have a column with variable content (from row-to-row) that needs a GROUP BY

here's an example with some sample data

SELECT [CATEGORY_ID] = 'A', [Qty] = 1
INTO #TEMP
UNION ALL SELECT 'A', 5
UNION ALL SELECT 'B', 3
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'A', 3
UNION ALL SELECT 'C', 1
UNION ALL SELECT 'C', 8
UNION ALL SELECT 'B', 6
UNION ALL SELECT 'B', 5
UNION ALL SELECT 'C', 2


SELECT 1 AS Section,
       CATEGORY_ID,
       SUM(Qty) AS Qty
FROM	#TEMP
-- WHERE ...
GROUP BY CATEGORY_ID
UNION ALL
SELECT 2 AS Section,
       NULL AS CATEGORY_ID,
       SUM(Qty) AS Qty
FROM	#TEMP
-- WHERE ...
ORDER BY Section, CATEGORY_ID
GO
DROP TABLE #TEMP
GO
10

Please look at the following query simplified from previous version:

SELECT SUBSTRING(a.Code,1,5) [Zip Code]
, COUNT(SUBSTRING(a.Code,1,5)) AS Count
,ROW_NUMBER() OVER (ORDER BY SUBSTRING(a.Code,1,5)) 'RowNumber'
FROM TableA AS a
INNER JOIN (SELECT COUNT(*) AS total FROM TableA) AS t ON (1=1)
GROUP BY SUBSTRING(a.Code,1,5), t.total

UNION ALL
SELECT '' AS Code,
SUM(COUNT(SUBSTRING(ac.Code,1,5))) AS Count,
'' AS RowNumber
FROM TableA AS ac

This still gives me the error..

Thanks.

11

This is how I resolved it:

UNION ALL
SELECT '' AS Code,
SUM(t1.Count1),
FROM
(
SELECT COUNT(Code) AS Count1
FROM TableA
INNER JOIN (SELECT COUNT(*) AS total FROM TableA) AS t ON (1=1)
GROUP BY Code, t.total
) t1

12

https://technet.microsoft.com/en-us/library/ms175939(v=sql.90).aspx

13

Not sure what you are after here (your revised code does the same job, so I would have the same question about that too :slightly_smiling: )

COUNT(xxx) will count the number of rows in the resultset that are not-NULL for the "xxx" column/expression.

SUM(xxx) will total the "xxx" column / expression

Depends on whether you are using the side effect of (not) counting NULLs in your result? I'm guessing, given that you have a SUBSTRING in there, that you are intending something else. SUBSTRING is only going to be NULL if the whole [ac.Code] column is NULL, hence I reckon you were intending to do some "work"on the first 5 characters (digits??) of [ac.Code]

If you literally just want the total (SUM) count of the non-NULL values in [ac.Code] then replace

SUM(COUNT(SUBSTRING(ac.Code,1,5))) AS Count

with

COUNT(ac.Code) AS Count

Your second, successful, code looks like it is going around the houses more than it needs to (I worry about that, because either you intended it to :slight_smile: but are not using the detail results, or you did not intend it to (in which case there is a smarter, more efficient, way to write the code)

As it stands the next person who needs to take a look at your code might ask "Is this meant to do A? ... or B?".

SELECT COUNT(Code) AS Count1
FROM TableA
INNER JOIN (SELECT COUNT(*) AS total FROM TableA) AS t ON (1=1)
GROUP BY Code, t.total

This will produce a resultset (with multiple rows unless all rows have the same, single, value in the [Code] column) with a COUNT of the number of rows where TableA.Code is non-NULL.

It will "GROUP BY" Code and t.total

t.total is the total number of rows in TableA. This total is (Cartesian) joined to every row in the outer query.

I can't see that GROUP BY on t.total is doing anything as it is the same on every row?

So basically you are taking a COUNT(Code) GROUP BY Code and then SUM()ing them. I can't see what that is intended to achieve that

SELECT COUNT(Code) AS Count1
FROM TableA

would not do (more efficiently and without ambiguity) ...

But maybe I am missing something crucial?

14

To quickly get total of column, cut/paste into excel, highlight and look at footer.

15

Seriously?

Well, I suppose for this example of 3 rows of data that would be the case ...

16

And if you have 10 million rows?

17

Bit more tricky :slightly_smiling: as would, I suspect?, a report needing totals at the bottom of each section. I think it probably needs a SQL solution - which can then, also, be easily adapted to the situation / requirements - in the event that that changes.

18

check out group by rollup

19

Just curious... why do you do it that way? It seems like a relatively huge amount of code and it scans the table twice instead of just once. Gerald's nice, short WITH ROLLUP code is nearly perfect on this except he's missing an ORDER BY to guarantee the order.

20

You're making at least 1 unnecessary set of hits on the table. Use Gerald's WITH ROLLUP code instead.