Loading ...
Sorry, an error occurred while loading the content.

Count over multiple tables

Expand Messages
  • williamvdw2004
    I would like to get the total number of records for 2 tables combined. This is as far as I got: SELECT COUNT(*) FROM TBL1 UNION SELECT COUNT(*) FROM TBL2; This
    Message 1 of 7 , Dec 1, 2004
    • 0 Attachment
      I would like to get the total number of records for 2 tables combined.

      This is as far as I got:

      SELECT COUNT(*) FROM TBL1 UNION SELECT COUNT(*)
      FROM TBL2;

      This gives me 2 rows one with the number of records in TBL1 and one
      with the number of records in TBL2. How can I add these values?

      Regards
      William
    • Alan McDonald
      ... YOu could create a view of this SQL statement then select the cum of your count fields. Alan
      Message 2 of 7 , Dec 1, 2004
      • 0 Attachment
        >
        > I would like to get the total number of records for 2 tables combined.
        >
        > This is as far as I got:
        >
        > SELECT COUNT(*) FROM TBL1 UNION SELECT COUNT(*)
        > FROM TBL2;
        >
        > This gives me 2 rows one with the number of records in TBL1 and one
        > with the number of records in TBL2. How can I add these values?
        >
        > Regards
        > William
        >

        YOu could create a view of this SQL statement then select the cum of your
        count fields.
        Alan
      • Adomas Urbanavicius
        ... select count(*) + (select count(*) from tb_tb2) from tb_tb2
        Message 3 of 7 , Dec 1, 2004
        • 0 Attachment
          williamvdw2004 wrote:

          >I would like to get the total number of records for 2 tables combined.
          >
          >This is as far as I got:
          >
          >SELECT COUNT(*) FROM TBL1 UNION SELECT COUNT(*)
          >FROM TBL2;
          >
          >This gives me 2 rows one with the number of records in TBL1 and one
          >with the number of records in TBL2. How can I add these values?
          >
          >
          >

          select count(*) + (select count(*) from tb_tb2)
          from tb_tb2
        • Ian A. Newby
          Hi William, you can use subqueries in FB 1.0 - 1.5 SELECT (select COUNT(*) FROM TBL1) + (SELECT COUNT(*) FROM TBL2) from rdb$database; in Firebird 2 I believe
          Message 4 of 7 , Dec 1, 2004
          • 0 Attachment
            Hi William,

            you can use subqueries in FB 1.0 - 1.5

            SELECT (select COUNT(*) FROM TBL1) + (SELECT COUNT(*)
            FROM TBL2) from rdb$database;

            in Firebird 2 I believe you can use derived tables...

            select count(*) from (select 1 from tbl1 union select 1 from tbl2);

            Regards

            Ian Newby
          • Kadri Izer
            this is (can be) the solution ... select first 1 ((SELECT COUNT(*) FROM TBL1 ) + (SELECT COUNT(*) FROM TBL2)) from tbl2 // (or tbl1) kadri. ... From:
            Message 5 of 7 , Dec 1, 2004
            • 0 Attachment
              this is (can be) the solution ...
              select first 1
              ((SELECT COUNT(*) FROM TBL1 ) + (SELECT COUNT(*) FROM TBL2))
              from tbl2 // (or tbl1)

              kadri.

              -----Original Message-----
              From: williamvdw2004 [mailto:williamvdw2004@...]
              Sent: Wednesday, December 01, 2004 10:15 AM
              To: firebird-support@yahoogroups.com
              Subject: [firebird-support] Count over multiple tables




              I would like to get the total number of records for 2 tables combined.

              This is as far as I got:

              SELECT COUNT(*) FROM TBL1 UNION SELECT COUNT(*)
              FROM TBL2;

              This gives me 2 rows one with the number of records in TBL1 and one
              with the number of records in TBL2. How can I add these values?

              Regards
              William







              Yahoo! Groups Links
            • Ann W. Harrison
              ... That will, I think, give you the value 2 in all cases. Regards, Ann
              Message 6 of 7 , Dec 1, 2004
              • 0 Attachment
                At 04:14 AM 12/1/2004, Ian A. Newby wrote:

                >in Firebird 2 I believe you can use derived tables...
                >
                >select count(*) from (select 1 from tbl1 union select 1 from tbl2);

                That will, I think, give you the value 2 in all cases.

                Regards,


                Ann
              • Arno Brinkman
                Hi, ... 1, because it is not a UNION ALL :-) Just change the UNION to UNION ALL and it s ok. Regards, Arno Brinkman ABVisie -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                Message 7 of 7 , Dec 1, 2004
                • 0 Attachment
                  Hi,

                  > >in Firebird 2 I believe you can use derived tables...
                  > >
                  > >select count(*) from (select 1 from tbl1 union select 1 from tbl2);
                  >
                  > That will, I think, give you the value 2 in all cases.

                  1, because it is not a UNION ALL :-)
                  Just change the UNION to UNION ALL and it's ok.

                  Regards,
                  Arno Brinkman
                  ABVisie

                  -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                  Firebird open source database (based on IB-OE) with many SQL-99 features :
                  http://www.firebirdsql.org
                  http://www.firebirdsql.info
                  http://www.fingerbird.de/
                  http://www.comunidade-firebird.org/

                  Support list for Interbase and Firebird users :
                  firebird-support@yahoogroups.com

                  Nederlandse firebird nieuwsgroep :
                  news://80.126.130.81
                Your message has been successfully submitted and would be delivered to recipients shortly.