Home » SQL & PL/SQL » SQL & PL/SQL » count and sum from different tables
count and sum from different tables [message #18871] Tue, 19 February 2002 12:00 Go to next message
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 Go to previous messageGo to next message
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;
Re: count and sum from different tables [message #18882 is a reply to message #18871] Tue, 19 February 2002 23:41 Go to previous message
Tariq Zia lakho
Messages: 8
Registered: February 2002
Junior Member
SQL 1 select count(a.empno) ename, sum(b.sal) sal
2 from emp a ,emp1 b
3 where a.deptno=b.deptno
4 and a.empno = b.empno;

ENAME SAL
--------- ---------
14 29025
Previous Topic: Trigger
Next Topic: Logging events
Goto Forum:
  


Current Time: Mon Sep 27 08:08:07 CDT 2021