INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Divide 2 counts

Divide 2 counts

(OP)
How do I divide by 2 counts in DB2?  It should be so simple, but I can't figure it out...

(SELECT COUNT(*) FROM TABLE WHERE SALMATCH = 'N')/(SELECT COUNT(*) FROM TABLE)

Thanks :)

RE: Divide 2 counts

Try this:

CODE

SELECT C1/C2
FROM
(SELECT COUNT(*) AS C1 FROM SYSIBM.SYSDUMMY1) T1
,(SELECT COUNT(*) AS C2 FROM SYSIBM.SYSDUMMY1) T2

RE: Divide 2 counts

(OP)
I have tried

CODE

SELECT C1/C2
FROM
(SELECT COUNT(*) FROM TABLE WHERE SALMATCH = 'N' AS C1 FROM SYSIBM.SYSDUMMY1) T1
,(SELECT COUNT(*) FROM TABLE AS C2 FROM SYSIBM.SYSDUMMY1) T2

and

CODE

SELECT C1/C2
FROM
(SELECT COUNT(*) WHERE SALMATCH = 'N' AS C1 FROM TABLE) T1
,(SELECT COUNT(*) AS C2 FROM TABLE) T2

but I get the error

CODE

Error: SQL0199 - Keyword AS not expected. Valid tokens: ) FETCH ORDER UNION EXCEPT. (State:37000, Native Code: FFFFFF39)

Can you spot what I'm doing wrong?
Thanks

RE: Divide 2 counts

The "AS Cx" should be before the FROM (as the example shows) - you have it after the FROM.

RE: Divide 2 counts

KHS,
papadba is correct. The AS C1 piece of code names the columns of the table that is subsequently used in the outer select. If you cut an paste my code, it will actually work.

In order to correct your code, you need to take any reference to sysibms.sysdummy1`out and replace with your own table. Fotrt example:

CODE

SELECT C1/C2
FROM(SELECT COUNT(*) AS C1 FROM TABLE WHERE SALMATCH = 'N') T1,
    (SELECT COUNT(*) AS C2 FROM TABLE) T2

Hope this helps.

Marc

RE: Divide 2 counts

(OP)
Ah, so I had the WHERE clause in the wrong place.  Thanks.

However, because both counts were integers, I actually had to CAST one of them as a decimal.  My final statement was...

CODE

SELECT (C1/C2)*100
FROM (SELECT CAST(COUNT(*) AS DECIMAL) AS C1 FROM TABLE WHERE SALMATCH = 'N') T1,
    (SELECT COUNT(*) AS C2 FROM TABLE ) T2

Thanks both for your help.

RE: Divide 2 counts

You're welcome - thanks for the update pipe

RE: Divide 2 counts

if the selects are from the same table then there is another option.

select count_salmatch / total_count
from (
select sum(case
           when salmatch = 'N' then 1
           else 0
           end) count_salmatch
      ,count(*) total_count
  from table
) temp

Regards

Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Divide 2 counts

(OP)
Thanks Frederico,

I did have to make one change to this though, as I did before, as the result returns 0 unless the case statement is CAST as a decimal.  (I also multiplied by 100 to get the percentage).

CODE

SELECT (COUNT_SALMATCH / TOTAL_COUNT)*100
FROM (
SELECT CAST(SUM(CASE
           WHEN SALMATCH = 'N' THEN 1
           ELSE 0
           END) AS DECIMAL) COUNT_SALMATCH
      ,COUNT(*) TOTAL_COUNT
  FROM CSALIFL
) TEMP        

Cheers

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close