0

I have 2 tables groups and contact and i fetch all groups with their total number of contact in contact table. Like in groups table i have values

 group1 group2

in contact table i have

 myname group1 myname1 group1 myname2 group1

now i want all groupname with their contact count like

 group1 3 group2 0

i used :

SELECT g.gid,g.groupname,g.TYPE,g.DATE,COUNT(*)AS cnt,1 FROM groupname g,contacts c WHERE g.gid=c.gid AND uid=1 GROUP BY groupname

But i got those group which have value.

Improve this question

3 Answers 3

Reset to default
1

use Left Outer Join instead of old style of Inner Join

SELECT g.groupname, Count(c.gid) as Cnt FROM groups g LEFT OUTER JOIN contact c ON g.gid = c.gid WHERE uid = 1 GROUP BY g.groupname
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

You can use a correlated sub-query:

SELECT g.gid, g.groupname, g.TYPE, (SELECT COUNT(*) FROM contacts c WHERE c.gid = g.gid) AS cnt FROM groupname g WHERE uid=1
Improve this answer

3 Comments

if we have 10K records, then above sub query will execute for 10K times..!
@VishalZanzrukia: currently the table contains two records ;) I'm more familiar with SQL-Server where this is not an issue.
but I think it is just for question understanding purpose.
0 
SELECT g.gid,g.groupname,g.TYPE,g.DATE,COUNT(*)AS cnt,1 FROM groupname g LEFT JOIN contacts c on c.group_name = g.group_name WHERE g.gid=c.gid AND uid=1 GROUP BY groupname
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.