count multiple tables in sql

Question

I have a database that has a table for each day making it a huge database consisting of so many months and even years. Now, I wanna count the records of all the tables in each month. I mean, I want the count of all the records in all tables but monthly apart.

I already used this query:

select
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000601] where mti=200 )+
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000602] where mti=200 )+
(SELECT count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000603] where mti=200 )+...

witch is useful but it won't give me the result for each month. it gives me the total count of all the tables existing in the query. Thx everyone.

Hi - having a table per day is normally a really bad design and it's generally much better to have a single table with a "date loaded" (or similar) column. Assuming you can't change the design, how do you know which tables apply to which month? From the sample query it seems like the table names are sequential and don't contain any information about the date they refer to — NickW
– NickW, Commented May 10, 2023 at 10:42
Assume this MS Sqlserver? Dynamic sql is a way you can do theis sort of thing, when someone chooses this approach. Plenty of example to do something similar, but it's basically code. — Tony Hopkinson
– Tony Hopkinson, Commented May 10, 2023 at 10:43
How do you know which month a table belongs to? Does 14000601 imply something? — jarlh
– jarlh, Commented May 10, 2023 at 11:36
Yes the design is not so appealing but that's just protocols and I can't change that unfortunately. by the way 14000601 is the date of the very day that the table belongs to. So the tables are named after the date of that day and also they are named and ordered based on date very neatly. — Emma
– Emma, Commented May 10, 2023 at 12:15

NickW · Accepted Answer · 2023-05-10 13:09:51Z

0

You should just be able to hard-code the relevant year/month/day values into your query against each table, UNION all individual table queries together and then query that result to give what ever final result you need.

This SQL will give you an example of how to do this; obviously you may need to adjust it to meet your exact requirements

WITH ALL_DATA AS 
(SELECT '1400' TABLE_YEAR, '06' TABLE_MONTH, '01' AS TABLE_DAY, count(*) AS TABLE_COUNT FROM [Rayanparsi].[dbo].[TransRecordTable_14000601] where mti=200
 UNION
 SELECT '1400' , '06', '01', count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000602] where mti=200
 UNION
 SELECT '1400' , '06', '01', count(*) FROM [Rayanparsi].[dbo].[TransRecordTable_14000603] where mti=200
)
SELECT TABLE_YEAR, TABLE_MONTH, SUM(TABLE_COUNT)
FROM ALL_DATA
GROUP BY TABLE_YEAR, TABLE_MONTH
;

answered May 10, 2023 at 13:09

NickW

10k2 gold badges10 silver badges26 bronze badges

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

2 Comments

Emma Over a year ago

Wow thank you so much. You just saved me a lot of time and work. That got me to the result that I wanted... I just added all the tables to the query considering the format of the query. but I was wondering if I can add one more task to the query witch is summing the values of a field that exists in all tables. is that even possible?

NickW Over a year ago

Yes, just add “, sum (column_name)” after the count in each query

Collectives™ on Stack Overflow

count multiple tables in sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related