Selecting Count from multiple tables and returning them all in one row - SQL Server

Question

so the issue I'm trying to solve is I have 8 tables with data in them. And a 9th table, with a field for each table, that I want to store the count of each of the previous 8 tables. I'm able to return the counts however instead of one count per field, I have 8 rows just populating the first field. Each of the 8 table names is a field name in the 9th table. Here's my code:

SELECT COUNT(SubID) as Sent_Members FROM Sent_Members
UNION ALL
SELECT COUNT(SubID) as Sent_Shoppers FROM Sent_Shoppers
UNION ALL
SELECT COUNT(SubID) as Open_Members FROM Open_Members
UNION ALL
SELECT COUNT(SubID) as Open_Shoppers FROM Open_Shoppers
UNION ALL
SELECT COUNT(SubID) as Click_Members FROM Click_Members
UNION ALL
SELECT COUNT(SubID) as Click_Shoppers FROM Click_Shoppers
UNION ALL
SELECT COUNT(SubID) as Unique_Click_Members FROM Unique_Click_Members
UNION ALL
SELECT COUNT(SubID) as Unique_Click_Shoppers FROM Unique_Click_Shoppers

I'm guessing I should be using something instead of Union, but I'm not sure what that would be.. Thanks!

You want to store the counts of each table in another table? For how long? If you insert a row 10 minutes from now, do you need to update this 9th table? How about if you delete 20 rows tomorrow? What is the purpose of STORING this data, and why does it need to be in a row instead of columns? Also do you have any idea how much more expensive it is to run a count directly against the table instead of using the metadata / catalog views (e.g. sys.partitions)? — Aaron Bertrand
– Aaron Bertrand, Commented Apr 16, 2013 at 20:13
Do you need to store this information in the "9th table"? Does that table exist? Or do you only need a query that shows you 8 columns containing the COUNTs from the tables? — RichardTheKiwi
– RichardTheKiwi, Commented Apr 16, 2013 at 20:34

Aaron Bertrand · Accepted Answer · 2013-04-16 20:21:47Z

4

This looks uglier but it is hundreds of times more efficient than doing 8 subqueries with a complete table scan in each one.

;WITH r AS
(
  SELECT t.name, rc = SUM(p.rows)
   FROM sys.tables AS t
   INNER JOIN sys.partitions AS p
   ON t.[object_id] = p.[object_id]
   WHERE p.index_id IN (0,1)
   AND t.name IN 
   (
    N'Sent_Members',
    N'Sent_Shoppers',
    N'Open_Members',
    N'Open_Shoppers',
    N'Click_Members',
    N'Click_Shoppers',
    N'Unique_Click_Members',
    N'Unique_Click_Shoppers'
   )
   GROUP BY t.name
)
SELECT * FROM r 
PIVOT (MAX(rc) FOR name IN 
(
 [Sent_Members],
 [Sent_Shoppers],
 [Open_Members],
 [Open_Shoppers],
 [Click_Members],
 [Click_Shoppers],
 [Unique_Click_Members],
 [Unique_Click_Shoppers]
) AS p;

answered Apr 16, 2013 at 20:21

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RichardTheKiwi Over a year ago

Just a comment: sqlblog.com/blogs/kalen_delaney/archive/2009/12/07/… Disclaimer: I use sys.partitions all the time, just pointing out that it's not COUNT()

Aaron Bertrand Over a year ago

@RichardTheKiwi Completely understood. However if the system is active and you're worried about te accuracy of the count, what difference does it make? If your SELECT COUNT blocks writers until the count is done, your exact count is inaccurate as soon as it's finished anyway, since all the writers that have been blocked now can sweep in and change the count.

mccee · Accepted Answer · 2013-04-16 20:23:03Z

3

Here you go:

INSERT INTO [Table9]
SELECT 
    (SELECT COUNT(*) FROM Sent_Members) AS Sent_Members,
    (SELECT COUNT(*) FROM Sent_Shoppers) AS Sent_Shoppers,
    (SELECT COUNT(*) FROM Open_Members) AS Open_Members,
    (SELECT COUNT(*) FROM Open_Shoppers) AS Open_Shoppers,
    (SELECT COUNT(*) FROM Click_Members) AS Click_Members,
    (SELECT COUNT(*) FROM Click_Shoppers) AS Click_Shoppers,
    (SELECT COUNT(*) FROM Unique_Click_Members) AS Unique_Click_Members,
    (SELECT COUNT(*) FROM Unique_Click_Shoppers) AS Unique_Click_Shoppers

edited Apr 16, 2013 at 20:23

answered Apr 16, 2013 at 20:10

mccee

1,66711 silver badges19 bronze badges

9 Comments

RichardTheKiwi Over a year ago

Since you're doing an INSERT INTO, the "as ..." aliases are spurious

user1180997 Over a year ago

Thanks this works.. but to make things more complicated what if I'm unable to use INSERT INTO.. This query is running in a platform that only allows SQL statements to start with SELECT.

mccee Over a year ago

What is your ultimate goal with [Table9]? Do you want to recreate it on-the-fly? Is it just a summary table at any given point int time?

Aaron Bertrand Over a year ago

What "platform" only allows selects? And how do you plan to insert data into another table if you can only select?

Aaron Bertrand Over a year ago

You can't perform an INSERT into an existing table using a command that starts with SELECT. What ungodly platform is this that it restricts you to SELECT?

|

Collectives™ on Stack Overflow

Selecting Count from multiple tables and returning them all in one row - SQL Server

2 Answers 2

2 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related