• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1678
  • Last Modified:

Row Count of Multiple tables in single table

I have a database with several tables and I want to find out how many rows are added each day.
Each table has a field MaintDate which is the date the row was added.  Therefore the query below would give what I want for Table1.

select maintdate,count(maintdate) As NumberofUpdates from Table1 where maintdate >20071001 group by maintdate

There are many tables and I want the output in tabular format e.g

Date           Table1Updates, Table2Updates
01/01/07        78,65
02/01/07        555,666
03/01/07      100,200
0
AlHal2
Asked:
AlHal2
  • 2
1 Solution
 
Ashish PatelCommented:
May be you can try Union between select statements like this given below for sample for 2 tables

Select maintdate,count(maintdate) As NumberofUpdates As Table1, NULL As Table2 from Table1 where maintdate >20071001 group by maintdate
UNION
Select maintdate, NULL As Table1, count(maintdate) As NumberofUpdates As Table2 from Table1 where maintdate >20071001 group by maintdate
 
and so on....you can add more statements and note the number of columsn for tables you use.
0
 
AlHal2Author Commented:
Then I won't get the table heading going across.  I'll just have one long column of results.
0
 
AlHal2Author Commented:
Please let me know if I've not been clear.
0
 
yuchingCommented:
May this can help :)

SELECT maintdate, SUM(Table1Updates) AS Table1Updates, SUM(Table2Updates) AS Table2Updates
FROM
(
SELECT maintdate,count(*) As Table1Updates, 0 As Table2Updates FROM Table1
WHERE maintdate >20071001 GROUP BY maintdate

UNION ALL

SELECT maintdate, 0 As Table1Updates, count(*) As Table2Updates FROM Table2
WHERE maintdate >20071001 GROUP BY maintdate
) T
GROUP BY maintdate

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now