Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query using DISTINCT and WHERE clause

Author  Topic 

tinkles
Starting Member

2 Posts

Posted - 2015-03-31 : 16:32:18
Hi,

I have two tables:

1) MyTable1 has one field: terms.
This table has 200,000 rows.

2) MyTable2 has three fields: terms, abstract_ID, and category
This table has 5 milllion rows.

I want to write a query to find the COUNT for DISTINCT abstract_IDs FROM mytable2 ...for every term in myTable1 that matches with terms in myTable2.

So, the count should return the number of distinct or unique abstract_ids From mytable2....for each of the 200,000 terms in mytable1.

I can't figure out the query. I am running this in Oracle. Any help would be great!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-31 : 16:58:10
[code]select count(distinct t2.abstract_id)
from
MyTable1 t1
inner join
MyTable2 t2
on t1.terms = t2.terms[/code]



I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers
Go to Top of Page

tinkles
Starting Member

2 Posts

Posted - 2015-03-31 : 23:30:09
I actually needed the query to return each term with its corresponding count value. But you put me in the right direction and all I needed to do was add
SELECT t2.term, COUNT (distinct t2.abstract_id) and a group by clause in the end.

Thank you for your help!
Go to Top of Page
   

- Advertisement -