Q:

Joining of two tables with group by and count

Hi I got User and Transaction tables and below is the structure of those tables.
User Table
username,transactioncode are the fields
Transaction Table
rolename,transactioncode are the fields 
I have to get username,transactioncode from User table and combination of those two fields should be distinct and should get the count of rolename for that particular transaction code from Transaction Table.
Below is my query which is returning wrong count:
select u.username,u.transactioncode,count(t.rolename) 
from user u,transaction t 
where u.transactioncode=t.transactioncode 
group by u.username,u.transactioncode;

A:

First use proper explicit join syntax and other is to use distinct 
select u.username,u.transactioncode,count(distinct t.rolename) roles
from user u 
inner join transaction t on u.transactioncode=t.transactioncode 
group by u.username, u.transactioncode;

In other way, you could also use subquery
select username, transactioncode, 
      (select count(distinct rolename) from Transaction 
       where transactioncode = u.transactioncode) as roles
from user u;

However, distinct may be redundant here but i don't know how your tables are looks and what data are into. 
You could also first do aggregation and join with user table
select u.username, u.transactioncode, t.roles  
from (select transactioncode, count(distinct rolename) roles
      from Transaction t
      group by transactioncode) t 
inner join user u on 
           u.transactioncode = t.transactioncode;

