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

Neena John · #1 (**permalink**) 07-16-09, 05:47

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?

sathyaram_s · #2 (**permalink**) 07-16-09, 07:15

select sum(ct) from table(your query) as t(ct)

Stealth_DBA · #3 (**permalink**) 07-16-09, 08:07

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.

r937 · #4 (**permalink**) 07-16-09, 08:41

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?

Stealth_DBA · #5 (**permalink**) 07-16-09, 08:55

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;

r937 · #6 (**permalink**) 07-16-09, 09:15

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

n_i · #7 (**permalink**) 07-16-09, 09:29

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)

r937 · #8 (**permalink**) 07-16-09, 09:34

Quote:

Originally Posted by n_i

I would rephrase the above statement as ...

well stated

what do you think of the SYSDUMMY1 solution?

tonkuma · #9 (**permalink**) 07-16-09, 09:36

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)
;

n_i · #10 (**permalink**) 07-16-09, 11:44

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.

dav1mo · #11 (**permalink**) 07-16-09, 12:35

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

Stealth_DBA · #12 (**permalink**) 07-16-09, 12:47

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).

Internet Services

Web Development

Digital Marketing

Consumer Tech