Row Count of Multiple tables in single table

Posted on 2007-10-08
Last Modified: 2012-05-05
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
Question by:AlHal2
    LVL 23

    Expert Comment

    by:Ashish Patel
    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
    Select maintdate, NULL As Table1, count(maintdate) As NumberofUpdates As Table2 from Table1 where maintdate >20071001 group by maintdate
    and so can add more statements and note the number of columsn for tables you use.

    Author Comment

    Then I won't get the table heading going across.  I'll just have one long column of results.

    Author Comment

    Please let me know if I've not been clear.
    LVL 11

    Accepted Solution

    May this can help :)

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


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


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now