CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Potatooo
Potatooo

Reputation: 57

How can I count column 2 with other column

SELECT DISTINCT T1.ID , COUNT(T2.OPT_ID) 
FROM TABLE1 , TABLE2
WHERE T1.TRACK_ID = T2.TRACK_ID
GROUP BY T1.ID, T2.OPT_ID

Output: 

ID (COLUMN1)        OPT_ID(COLUMN 2)
1000000001082157827 132665
1000000001082157827 132564
1000000001082157827 117345
1000000001082157827 11565
1000000001082157827 124546
1000000001082157827 117495
1000000001082157827 11564
1000000001082157827 11564
1000000001082157827 117495

If I count T2.OPT_ID it only count 1 per T1.ID but it's okay if i only count t2.opt_id but need these two columns. result should look like this

ID (COLUMN1)        | OPT_ID(COLUMN 2)
000000001082157827  | 7

There also some case that OPT_ID have two same id. I should get same total number (7 not 9)

Upvotes: 1

Views: 64

Answers (3)

praveen
praveen

Reputation: 75

Try using the following query:

SELECT id ,(SELECT Sum(Count(*))
 FROM t12 GROUP BY opt_id HAVING Count(*)=1) AS count1
 FROM t12---change the table name
 GROUP BY id;

Upvotes: 1

Sivaprasath Vadivel
Sivaprasath Vadivel

Reputation: 541

select t1.id,count(distinct t2.opt_id) 
from table1,tabel2 
where t1.track_id=t2.track_id 
group by t1.id

I think this will solve your problem.

Upvotes: 0

Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3330

For the current dataset the below query should give you the result

SELECT T1.ID , COUNT(*) 
FROM TABLE1 T1 JOIN TABLE2 T2
ON T1.TRACK_ID = T2.TRACK_ID 
GROUP BY T1.ID

In the given example, you dont have a table2, I am not sure how table2 influences the result set. If you are using group by for any particular column, you need not give distinct for that column again. Both serves the same purpose (Group by is preferred over distinct when performance is considered).

Upvotes: 1

Related Questions