Uregent finding difference between count of two coulumns

[lakshmananl]

hi,
i am doing a report generation work in which i take the count of two
columns. and i need to calculate the difference of the counted value as
a new column. for example in SQL we use
<B>difference(count(a.column1),count(a.column2)) as alias <B>

what will be the equivalent statement in DB2

i tried the same in DB2 but i get error.
Kindly help me in this

regards
lakshmanan

[Tonkuma]

What DBMS are you using?

MS SQL Server 2000 has DIFFERENCE function. But, it's written that
"Returns the difference between the SOUNDEX values of two character
expressions as an integer."

I couldn't find DIFFERENCE function in Oracle 10g SQL Reference.

[Tonkuma]

If you want difference of two counts, you can get it with this.

ABS(count(a.column1) - count(a.column2)) as alias

[lakshmananl]

hi i am using DB2 v7.2.9, i tried the example in SQL Server. and i need
the same to be done in the DB2.

the actual report should look like below
col1(count) col2(count) col3(diff of count)
1 1 0
3 1 2

like this i am able to do this in SQL server

kindly help me

regards
lakshmanan

[Dave Hughes]

lakshmananl wrote:

hi i am using DB2 v7.2.9, i tried the example in SQL Server. and i
need the same to be done in the DB2.

the actual report should look like below
col1(count) col2(count) col3(diff of count)
1 1 0
3 1 2

like this i am able to do this in SQL server

Diff of count? Weird syntax. What's wrong with a simple subtraction?

SELECT
COUNT(COL1) - COUNT(COL2) AS COUNTDIFF
FROM SOMETABLE

Or as Tonkuma suggested, throw in a call to ABS() if you don't want
negative values in the result:

SELECT
ABS(COUNT(COL1) - COUNT(COL2)) AS COUNTDIFF
FROM SOMETABLE
HTH,

Dave.

--