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

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.

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

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

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

lakshmananl wrote:
[color=blue]
> 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[/color]

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.

--