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.