How do I get the totals of all tables combined?

I have six tables that I need total of all of the records combined. I tried to make a query to add all the tables, but it is not working.
gigifarrowAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
From your question, it seems like your doing this in Access?  If so, create a query for each of the tables that gets the count and save them (SELECT COUNT(*) AS RECCOUNT FROM <TABLE>).  Save it something like, Table1Count, Table2Count, etc.

Then create a new query with each of the queries you saved as the "tables". SQL Will look like this:

SELECT [Table1Count].[RECCOUNT]+[Table2Count].[RECCOUNT]+[Table3Count].[RECCOUNT] AS TotalCount
FROM Table1Count, Table2Count, Table3Count;

JimD.
0
 
knightEknightCommented:
--add what?  the count of rows?  the sum of a column?

;with CTE as (
  select 'Table1' as TableName, sum(myColumn) as mySum, count(*) as myCount
  from Table1
  UNION
  select 'Table2' as TableName, sum(myColumn) as mySum, count(*) as myCount
  from Table2
  UNION
  select 'Table3' as TableName, sum(myColumn) as mySum, count(*) as myCount
  from Table3
  UNION
  select 'Table4' as TableName, sum(myColumn) as mySum, count(*) as myCount
  from Table4
  UNION
  select 'Table5' as TableName, sum(myColumn) as mySum, count(*) as myCount
  from Table5
  UNION
  select 'Table6' as TableName, sum(myColumn) as mySum, count(*) as myCount
  from Table6
)

select sum(mySum), sum(myCount)
from CTE
0
 
gigifarrowAuthor Commented:
count total record for each table then take all the records total from all tables.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
mmr159Commented:
SELECT
    (SELECT COUNT(*) FROM table1) +
    (SELECT COUNT(*) FROM table2) +
    (SELECT COUNT(*) FROM table3) +
    (SELECT COUNT(*) FROM table4) +
    (SELECT COUNT(*) FROM table5) +
    (SELECT COUNT(*) FROM table6)
0
 
Bhavesh ShahLead AnalysistCommented:
hi,

if you want row count for all tables then


SELECT object_name (i.id) TableName, rows as RowCnt
FROM sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = 'U')
WHERE indid < 2
--AND TABLENAME IN ('table1','table2','table3','table4','table5') --You give tablename too
ORDER BY TableName

0
 
Bhavesh ShahLead AnalysistCommented:

SELECT object_name (i.id) TableName, rows as RowCnt
FROM sysindexes i INNER JOIN sysObjects o ON (o.id = i.id AND o.xType = 'U')
WHERE indid < 2
--AND TABLENAME IN ('table1','table2','table3','table4','table5') --You give tablename too
ORDER BY TableName

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.