Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Location
    India
    Posts
    40

    Unanswered: Db2 Query to find the sum of record count of three tables

    There are three tables
    Current,History and archive.The contents of these tables are consolidated in one table called Full.

    I need to write a query in which i need to
    sum up the record count of Current,History and archive

    For example
    SELECT COUNT(*) AS C
    FROM CURRENT
    UNION
    SELECT COUNT(*) AS H
    FROM HISTORY
    UNION
    SELECT COUNT(*) AS A
    FROM ARCHIVE;

    Now i need to sum up the count values(c,h,a) in the same query.
    Can anyone help me on this?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    select sum(ct) from table(your query) as t(ct)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Neena John, If you want all 4 values returned, you can try this:
    Code:
    SELECT C, H, A, C + H + A AS TOTAL
    FROM (
          SELECT COUNT(*) AS C
          FROM CURRENT
          UNION ALL
          SELECT COUNT(*) AS H
          FROM HISTORY
          UNION ALL
          SELECT COUNT(*) AS A
          FROM ARCHIVE
         ) AS NT;
    PS I changed UNION to UNION ALL. If you ever had the same value returned from the COUNT functions (i.e. you had duplicate values), the UNION would remove the duplicate values. UNION ALL keeps all rows (including any duplicates). Granted, in this particular situation, that is not likely but it could happen in other situations.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    stealth, i hate to be the bearer of bad news, but a UNION query cannot assign three different column names to the same column -- in your code, there is only one column name, and it is C



    oh, and by the way, the same question was also asked in the SQL Server forum, and there is a much simpler solution

    if i recall correctly, however, i don't think db2 will allow a query without a FROM clause

    is there the equivalent of a DUAL table in db2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Aargh, that is what I get for trying to think before my first cup of coffee...

    Thanks for catching that r937 (and it is not bad news if it results in a correct answer).
    Here is a (hopfully) correct solution if you want all the values on one row:
    Code:
    SELECT SUM(CASE TAB_TYPE WHEN 'C' THE CNT ELSE 0 END) AS C
         , SUM(CASE TAB_TYPE WHEN 'H' THE CNT ELSE 0 END) AS H
         , SUM(CASE TAB_TYPE WHEN 'A' THE CNT ELSE 0 END) AS A
         , SUM(CNT) AS TOTAL
    FROM (
          SELECT 'C' AS TAB_TYPE , COUNT(*) AS CNT
          FROM CURRENT
          UNION ALL
          SELECT 'H' AS TAB_TYPE , COUNT(*) AS CNT
          FROM HISTORY
          UNION ALL
          SELECT 'A' AS TAB_TYPE , COUNT(*) AS CNT
          FROM ARCHIVE
         ) AS NT;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by r937
    is there the equivalent of a DUAL table in db2?
    why, yes, yes there is, i found it here

    Code:
    SELECT ( SELECT COUNT(*) FROM current )
         + ( SELECT COUNT(*) FROM history )
         + ( SELECT COUNT(*) FROM archive ) AS grand_total
      FROM SYSIBM.SYSDUMMY1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by r937
    if i recall correctly, however, i don't think db2 will allow a query without a FROM clause
    The SQL-92 standard specifies the FROM clause in a SELECT statement as mandatory. I would rephrase the above statement as "however, the simpler SQL Server solution makes use of a non-standard extension to SQL, which may not be available in databases that follow the standard".

    Here's another, standard-compliant solution:

    Code:
    SELECT status, count(*) from ( 
      SELECT 'Current' as status FROM current
      union all
      SELECT 'Historical' as status FROM history
      union all
      SELECT 'Archived' as status FROM archive )t
    group by rollup (status)
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by n_i
    I would rephrase the above statement as ...
    well stated

    what do you think of the SYSDUMMY1 solution?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you are using DB2 for LUW, you can use VALUES clause.
    Code:
    SELECT c, h, a
         , c + h + a AS sum
      FROM (VALUES
            ( (SELECT COUNT(*) FROM current)
             ,(SELECT COUNT(*) FROM history)
             ,(SELECT COUNT(*) FROM archive)
            )
           ) AS s(c, h, a)
    ;

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by r937
    what do you think of the SYSDUMMY1 solution?
    The standard does not have anything to say about dummy or dual tables, so this is a nice one, as long as the OP is happy. "select count(*) from (select 1 from current union all ...)" would also work I guess, so the question really is, how much SQL eye candy you can stand.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Hey Stealth,
    One thing I wanted to mention, performance wise even though it would not affect performance in this query with 3 results, was using a null instead of 0.

    SELECT SUM(CASE TAB_TYPE WHEN 'C' THE CNT ELSE 0 END) AS C
    , SUM(CASE TAB_TYPE WHEN 'H' THE CNT ELSE 0 END) AS H
    , SUM(CASE TAB_TYPE WHEN 'A' THE CNT ELSE 0 END) AS A

    The ELSE 0 would actually add 0 to your sum for every occurance that does not fit your when condition. Whereas, using an ELSE NULL would then ignore all sets that do not meet the when condition. I have seen this provide up to a 40% reduction in CPU costs on a reporting query.
    Dave

  12. #12
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Thanks for pointing that out, Dave. I think you have mentioned this before but it just hasn't seemed to sink into my thick head, yet. Keep at it and this old dog might actually learn a new trick...

    Personally, I like a few of the other techniques mentioned above (and in other places) like tonkuma's use of VALUES (if the result needs to be in one row) or Nick's (if the result need to be in multiple rows).

Posting Permissions

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