Selecting Sum of the Count From Tables in a DB
I have the following query:
SELECT SUM(count)
FROM (SELECT 'artist' AS table_name, COUNT(*) as count FROM artist
UNION
SELECT 'persons' AS table_name, COUNT(*) as count FROM persons
UNION
SELECT 'track' AS table_name, COUNT(*) as count FROM track)
It works as expected and开发者_Go百科 returns the proper count. But now when I do the following query I get the incorrect count:
SELECT SUM(count)
FROM (SELECT COUNT(*) as count FROM artist
UNION
SELECT COUNT(*) as count FROM persons
UNION
SELECT COUNT(*) as count FROM track)
Why is it the first query gets the proper count and the second one does not?
The UNION
eliminates duplicate values, so if two counts happen to be the same, one is eliminated and only one is summed. Try using UNION ALL
instead.
SELECT sum(count) FROM
(SELECT COUNT(*) as count FROM artist
UNION ALL
SELECT COUNT(*) as count FROM persons
UNION ALL
SELECT COUNT(*) as count FROM track)
If you can live with approximate, just count all tables in one go
SELECT
Total_Rows= SUM(st.row_count)
FROM
sys.dm_db_partition_stats st
WHERE
(index_id < 2) --cover both heaps and clustered indexes
AND
OBJECT_SCHEMA_NAME (object_id) <> 'sys' --ignore system stuff
This will run in a flash
Adopting your original, you can get count per table and overall in one go..
SELECT
*,
SUM(count) OVER () AS GrandTotal
FROM (SELECT 'artist' AS table_name, COUNT(*) as count FROM artist
UNION
SELECT 'persons' AS table_name, COUNT(*) as count FROM persons
UNION
SELECT 'track' AS table_name, COUNT(*) as count FROM track)
How is it inaccurate? One way might be because, in the second query, you hvae UNION
, and two or more rows contain the same value--because UNION
removes duplicate values. Try it with UNION ALL
, which returns all rows, not just unique ones.
精彩评论