I have a table that looks like this:

is there a way to display another row in the bottom that will display the total count of rows?

CodePudding user response：

I would agree with most of the other comments that acquiring a result set count would be more appropriate from the application code (which usually has a mechanism specifically for this purpose).

However...

If you must have a TSQL solution for your question, an option is to return the count in a separate column. This is different than returning it in a separate row, of course. There are pros & cons with each approach.

DROP TABLE IF EXISTS #Cities;

CREATE TABLE #Cities (
    City_Id INT,
    City VARCHAR(128)
);

INSERT INTO #Cities
VALUES
    (41,    'Athena'),
    (39,    'Beijing'),
    (35,    'London'),
    (30,    'Rio de Janeiro'),
    (28,    'Salt Lake City'),
    (18,    'Sochi'),
    (7 ,    'Sydney'),
    (4 ,    'Torino');

SELECT *, COUNT(*) OVER(ORDER BY (SELECT NULL)) AS Total
FROM #Cities;

--Count is properly reflected based on WHERE clause.
SELECT *, COUNT(*) OVER(ORDER BY (SELECT NULL)) AS Total
FROM #Cities
WHERE City LIKE 'S%';

--Be careful with this one--the COUNT(*) may not be what you expected.
SELECT TOP(4) *, COUNT(*) OVER(ORDER BY (SELECT NULL)) AS Total
FROM #Cities;

NOTE: be aware that this approach may not scale (perform) well for large result sets. Be sure to do some testing!

CodePudding user response：

As you know already, it should be done in the presentation layer. But if you just want to know if there is any way, then I would suggest to use UNION ALL

select cast(City_Id as varchar(10)) City_Id, City from Table1
union all
select 'Total' as City_Id, cast(count(*) as varchar(14)) from Table1

Here is the sql fiddle