woeful programming skills - help?

[Horace]

Hi,

I'm trying to get the rowcount from two tables where the key matches. This works fine. But then I also want to get the rowcount from the first table.

The code works if I take out the -- commented line.

Can't get this to work... Help!!

select COUNT(*) "KEY MATCH"
--      ,c.firstcount
from  TMP_JP_W_CARD_TRANSACTION a
           ,W_CARD_TRANSACTION b
           ,(select count(*) firstcount 
             from TMP_JP_W_CARD_TRANSACTION) c
where a.ACCOUNT_ORG = b.ACCOUNT_ORG

SQL>
      ,c.firstcount
       *
ERROR at line 2:
ORA-00937: not a single-group group function

[DaPi]

I'm not sure what you're trying to do, but if you put
GROUP BY c.firstcount
as the last line, the error will go away (might not give you what you want but . . . )

HANG ON ! ! ! !
that's a Cartesian product . . . . can't be right . . . no condition on "table" c

[DaPi]

Is this what you want:

select * from  
(select count(*) firstcount, ACCOUNT_ORG 
 from   TMP_JP_W_CARD_TRANSACTION
 group by ACCOUNT_ORG) a,
(select count(*) firstcount, ACCOUNT_ORG 
 from   TMP_CARD_TRANSACTION
 group by ACCOUNT_ORG) b
where a.ACCOUNT_ORG = b.ACCOUNT_ORG

[Horace]

Yes! You're right about the cartesian. Good job it didn't work, I still be here waiting for the results......

What I'm trying to do:-

I have 2 tables, TableA A and TableB B. I require output as follows..

[RowCount where A.key = B.key],[RowCount of A],[RowCount of B]

It's part of a reconciliation script I've been lumbered with, and ideally needs to be in a single sql statement.

[padders]

(see post in duplicate thread)