Divide 2 counts

[khsaunderson]

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

 

[MarcLodge]

Try this:

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

 

[khsaunderson]

I have tried

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

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

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

 

[papadba]

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

 

[MarcLodge]

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:

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

 

[khsaunderson]

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

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.

 

[papadba]

You're welcome - thanks for the update

 

[fredericofonseca]

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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[khsaunderson]

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

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