Home » SQL & PL/SQL » SQL & PL/SQL » Counts in SQL
Counts in SQL [message #18371] Tue, 29 January 2002 12:49 Go to next message
monsjic
Messages: 1
Registered: January 2002
Junior Member
I have data like below:

TABLE 1
C#_: I#_:
AAA 111

TABLE 2
C#_: I#_:
AAA 111
AAA 111

Table 1 has 1 record. Table 2 has 2 records.
All of the records are the same.

I want to get a count of matches on Cust # and Inv #
SELECT Count('x') AS COUNT
FROM Table1 INNER JOIN Table2 ON (Table1.C# = Table2.I#) AND (Table1.C# = Table2.I#);

But I want it to return the answer of 1 for the above sample data. The SQL above returns the answer of 2.

Does anyone know how to get my desired results?
Thanks
Re: Counts in SQL [message #18375 is a reply to message #18371] Tue, 29 January 2002 15:29 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
select count(distinct a.c1) from tab1 a,tab2 b
where a.c1=b.c1 and a.c2=b.c2;
Previous Topic: need a solution
Next Topic: Duplicates
Goto Forum:
  


Current Time: Sun Aug 09 19:06:37 CDT 2020