Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009

    Unanswered: sum of counts of same data existing in 2 different tables

    Hi all,

    There are two similar tables in 2 different schemas/databses that contain employee information.

    I have a requirement to get the list of the emp ids from those two tables whose sum of the count in the above tables is less than or equal to a user defined value.

    This is a wierd requirement but we need this for the data setup for one of our important performance tests.


    Emp_ids 1, 2 and 3 exist both in table A and table B.

    For emp_id 1, table A has 20 records for that emp_id and table B has 20 records => sum of counts = 40

    For emp_id 2, table A has 40 records for that emp_id and table B has 40 records => sum of counts = 80

    For emp_id 3, table A has 100 records for that emp_id and table B has 100 records => sum of counts = 100

    User wants only those emp_ids whose sum of count from both tables is less than or equal to 80.

    Thus the result of the sql must be emp_id 1 and 2 only.

    How do I do that?

    I tried few things but in vain.

    Any help is appreciated.

    Thanks in advance for your time and interest.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    SELECT emp
         , SUM(subtotal) AS total
      FROM ( SELECT emp
                  , COUNT(*) AS subtotal
               FROM A
                 BY emp
             UNION ALL
             SELECT emp
                  , COUNT(*) 
               FROM B
                 BY emp ) AS d
        BY emp
    HAVING total <= 80 | @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