Count (*) on two tables

Question

Iam little stucked with this sql query. I have got two tables

TbleName - usrs                     TbleName - idtb

name  |  cid                      cname   |   cid
-------------                     ----------------
james |  1100                      IT     |   1100
john  |  1200                      HR     |   1300
jack  |  1100                      QA     |   1200
bill  |  1300                      HD     |   1400
troy  |  1100


SELECT COUNT(*) as 'Total' FROM usrs u WHERE u.cid = 1100;

SELECT c.cname FROM idtb c WHERE c.cid = 1100;

My first query returns 3 and my second query returns IT , Now I want to join this 2 queries into one that would produe me result as this

        Total   |   Cname
        ------------------
          3     |    IT

I tried several ways and this worked

    SELECT COUNT(*) as 'Total',c.cname FROM usrs u JOIN  
    idtb c ON u.cid = c.cid WHERE u.cid = 1100
    GROUP BY u.cid

But the query does not seem to work when u.cid = 1400, since there are no names in usrs table that have cid value as 1400 and it returns empty result but I want the result to be

        Total   |   Cname
        -------------------
          0     |    HD

The query does not work if there are no records in usrs. I tried using left, right and full joins but did not figure it out. Any help is greatly appreciated.

There are no records for cid=1400 in the usrs table. Why would you expect it to return something different than 0? — Aheho, May 13 '14 at 14:20
@Aheho It does not return the result, it does not even return Cname HD if u.cid = 1400, it just returns empty rows — user3205479, May 13 '14 at 14:27

score 3 · Accepted Answer · answered May 13 '14 at 14:26

3

As you want to print all cname but counting usrs this is the query you need:

select a.cname, count(b.name)
  from idtb a left join usrs b on (a.cid = b.cid)
 group by a.cname

If you want to add a filter for some of then, just add the where clause.

See it here on fiddle: http://sqlfiddle.com/#!2/0acec/2

answered May 13 '14 at 14:26

Jorge Campos

22,647
7
56
87

@user3205479 Glad that I could help :) – Jorge Campos May 13 '14 at 14:49

score 2 · Answer 2 · answered May 13 '14 at 14:25

2

SELECT COUNT(*) as 'Total', idtb.cname
FROM idtb LEFT JOIN usrs
ON usrs.cid=idtb.cid
WHERE usrs.cid=1100
GROUP BY idtb.cname

answered May 13 '14 at 14:25

Nicholas Flees

1,943
3
22
30

score 1 · Answer 3 · 2014-05-13T14:31:43.930

1

This query will work

   SELECT COUNT(u.cid) as 'Total',c.cname 
   FROM usrs u left JOIN  
    idtb c ON u.cid = c.cid and u.cid = 1100
    GROUP BY u.cid

edited May 13 '14 at 14:31

answered May 13 '14 at 14:25

fmgonzalez · Answer 4 · 2014-05-13T14:28:12.943

0

Try this one.

SELECT i.cname, count(u.name) AS 'Total' 
  FROM idtb i LEFT JOIN usrs u ON (i.cid = u.cid)
 GROUP BY 1;

COUNT(1) is more efficient than *

I hope it works for you.

edited May 13 '14 at 14:28

answered May 13 '14 at 14:20

fmgonzalez

823
1
7
17

2

`COUNT(1)` is not more efficient than `COUNT(*)` http://stackoverflow.com/q/1221559/2186023 – DrCopyPaste May 13 '14 at 14:21
Thanks for the comment. I've always been wrong with this one. – fmgonzalez May 13 '14 at 14:23
It was my fault. Bad sintax in the ON clause. – fmgonzalez May 13 '14 at 14:32

score 0 · Answer 5 · answered May 13 '14 at 14:23

0

This should do the trick

    SELECT COUNT(*) as 'Total', c.cname 
    FROM idtb c 
    LEFT JOIN users u
    ON u.cid = c.cid 
    GROUP BY u.cid

answered May 13 '14 at 14:23

Evan Volgas

2,900
3
19
30

score 0 · Answer 6 · edited May 13 '14 at 14:54

0

SELECT COUNT(u.cid) as 'Total'
     , c.cname 
FROM usrs u 
RIGHT JOIN
idtb c 
    ON u.cid = c.cid 
WHERE c.cid = 1100
GROUP BY c.cname

edited May 13 '14 at 14:54

Matthew R.

4,332
1
24
39

answered May 13 '14 at 14:23

Lennart - Slava Ukraini

6,936
1
20
32

score 0 · Answer 7 · answered May 13 '14 at 14:28

0

There are several ways of doing this. The most simple might be using subqueries:

SELECT 
  (SELECT COUNT(1) FROM usrs WHERE cid = 1100) as Total,
  cname 
FROM 
  idtb c 
WHERE 
  c.cd = 1100;

answered May 13 '14 at 14:28

Allan Vital

376
4
18

score 0 · Answer 8 · answered May 13 '14 at 14:36

mysql will let you use an entire subquery in the select list, so long as the subquery produces a scalar value (the subquery must return only 1 column, and ONLY 1 row).

select (SELECT COUNT(*) as 'Total' FROM usrs u WHERE u.cid = 1100)
     , (SELECT c.cname FROM idtb c WHERE c.cid = 1100)

Count (*) on two tables

8 Answers8