| count and sum from different tables [message #18871] |
Tue, 19 February 2002 12:00  |
Deepa
Messages: 269
Registered: November 2000
|
Senior Member |
|
|
Hello!
I am having a problem with the following SQL:
select distinct a.NODE_ID, sum(a.total), count(distinct b.user_id)
from ULOG_HITS_PER_DAY_TBL a, ULOG_TBL b
where a.node_id=b.node_id
and a.node_id like '/test2/%'
and DAY >= to_date('02-12-2002','mm-dd-yyyy')
and DAY <= to_date('02-19-2002','mm-dd-yyyy')
and WHEN >= to_date('02-12-2002','mm-dd-yyyy')
and WHEN <= to_date('02-19-2002','mm-dd-yyyy')
group by a.node_id
The sum is actually 8 but this statement returns it as 64. How do I eliminate the duplicates?
Thanks.
Deepa
|
|
|
|
| Re: count and sum from different tables [message #18873 is a reply to message #18871] |
Tue, 19 February 2002 15:20  |
TUBIE
Messages: 1
Registered: February 2002
|
Junior Member |
|
|
Have a try!
SELECT C.A1, C.A2, D.B2
FROM
(SELECT A.ID A1, SUM(A.TOTAL) A2 FROM WANGTEST A GROUP BY A.ID) C,
(SELECT B.ID B1, COUNT(B.TOTAL) B2 FROM YONGTEST B GROUP BY B.ID) D
WHERE C.A1 = D.B1;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
