3 Replies Latest reply: Dec 11, 2012 10:38 AM by 977916 RSS

    Modifying COUNT(*) statement for multiple tables

      I need some advice modifying my existing COUNT(*) statement. I only have a basic knowledge of Oracle SQL, any advice would be greatly appreciated.

      The following COUNT(*) displays the number of rows with column "attribute_23" not null for each "assignment_number" . I am only interested in assignment_numbers that have more than one row with attribute23 not null.
      SELECT assignment_number, COUNT(*) number_recs
      FROM per_all_assignments_f
      WHERE attribute23 IS NOT NULL
      GROUP BY assignment_number HAVING COUNT(*) >1
      However, I want to add a "name" column from a 2nd table and "department" from a 3rd table to correspond with the assignment_number.
      person_id can be used to join the tables as it is present in each.

      COUNT(*) does not need to be carried out on the additional "name" and "department" columns, i just want to add this information to my current results.
      What is the best method of carrying out a COUNT(*) with multiple tables?

      I am using database version

      Advice would be greatly appreciated. Thanks
        • 1. Re: Modifying COUNT(*) statement for multiple tables

          welcome to the forum.

          Please read SQL and PL/SQL FAQ

          Additionally when you put some code or output please enclose it between two lines starting with {noformat}
          SELECT ...
          Please post CREATE TABLE and INSERT statement.
          Assuming that for each value of assignment_number you have only one corresponding name and one corresponding department you can group them without problem.
          SELECT t1.assignment_number, t2.name, t3.department
          , COUNT(*) number_recs
          FROM per_all_assignments_f t1
          JOIN t2 ON (t1.person_id = t2.person_id)
          JOIN t3 ON (t1.person_id = t3.person_id)
          WHERE t1.attribute23 IS NOT NULL
          GROUP BY t1.assignment_number, t2.name, t3.department
          HAVING COUNT(*) >1
          • 2. Re: Modifying COUNT(*) statement for multiple tables
            Frank Kulash

            Welcome to the forum!

            If, as Al siad, you do have one-to-many relationships, then you can do something like:
            WITH     paaf_agg     AS
                 SELECT    assignment_number
                 ,        COUNT (*)     AS number_recs
                 FROM        per_all_assignments_f
                 WHERE        attribute23     IS NOT NULL
                 GROUP BY  assignment_number 
                 HAVING        COUNT (*) > 1
            SELECT       t2.name
            ,       t3.department
            ,       pa.number_recs
            FROM       table_2  t2
            JOIN       table_3  t3  ON ...
            JOIN       paff_agg pa  ON ...
            depending on your reuirements, one or both of the joins might need to be outer-joins.


            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
            Explain, using specific examples, how you get those results from that data.
            Always say what version of Oracle you're using (e.g.
            See the forum FAQ {message:id=9360002}
            • 3. Re: Modifying COUNT(*) statement for multiple tables

              Thank you for your reply. That seems to have solved my problem.

              Thanks again, your help is much appreciated.