Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    19

    Unanswered: Multiple COUNT functions across multiple tables with multiple WHERE criteria

    I was wondering if it is possible to conduct the SELECT COUNT function across multiple tables and if so what it would look like.

    For example a count of all entries in Table A, Table B & Table C with specific criteria for selection from each table

    Table_A
    Entry 1
    Entry 2

    Table_B
    Entry 1
    Entry 2
    Entry 3

    Table_C
    Entry 1
    Entry 2
    Entry 3


    Code:
    SELECT COUNT(Table_a.entry, Table_b.entry, Table_c.entry) AS A, B, C
    FROM Table_A, Table_B, Table_C
    WHERE table_a.entry = 'Entry 1'
    OR table_b.entry IN ('Entry 1', 'Entry 2', 'Entry 3')
    OR table_c.entry IN ('Entry 1', 'Entry 2')
    Giving the answer with columns side by side
    A B C
    1 3 2

    Does that make sense? Is it possible

    Thanks

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    This is definitely one way of doing it. Another way could be

    Code:
    SELECT 'table_a', COUNT(Table_a.entry) AS table_count
    FROM Table_A
    WHERE table_a.entry = 'Entry 1'
    UNION
    SELECT 'table_b', COUNT(Table_B.entry) AS table_count
    FROM Table_B
    WHERE table_b.entry IN ('Entry 1', 'Entry 2', 'Entry 3')
    UNION
    SELECT 'table_c', COUNT(Table_C.entry) AS table_count
    FROM Table_C
    WHERE table_c.entry IN ('Entry 1', 'Entry 2');
    This would give you the following result:

    table_a, 1
    table_b, 3
    table_c, 2

    Alternatively you could also write the query:

    Code:
    SELECT A.table_count, B.table_count, C.table_count
    FROM (SELECT count(entity) as table_count FROM table_A WHERE table_a.entry = 'Entry 1') AS A,
    (SELECT count(entity) as table_count FROM table_B WHERE table_B.entity IN ('Entry 1', 'Entry 2', 'Entry 3')) AS B,
    (SELECT count(entity) as table_count FROM table_A WHERE table_c.entry IN ('Entry 1', 'Entry 2')) AS C;
    which would return the data as

    A.table_count B.table_count C.table_count
    1 3 2
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Can the subqueries put directly in SELECT clause?

    Code:
    SELECT (SELECT COUNT(*) FROM Table_A
             WHERE entry =  'Entry 1'                           ) AS a
         , (SELECT COUNT(*) FROM Table_B
             WHERE entry IN ('Entry 1' , 'Entry 2' , 'Entry 3') ) AS b
         , (SELECT COUNT(*) FROM Table_C
             WHERE entry IN ('Entry 1' , 'Entry 2')             ) AS c

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BridgeCat View Post
    Code:
    SELECT COUNT(Table_a.entry, Table_b.entry, Table_c.entry) AS A, B, C
    that won't work at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •