CODEWITHSUNDEEP
mysql
nicolaas
nicolaas

Reputation: 1881

Count(*) won't display when result is 0 when using group by

Sorry I couldn't think of a better title...

So I have this database where I have a table 'bibitems' (basicly texts) 'tags' (the tags the text is filed under) and 'taglinks' (Associative entity between a bibitem and a tag)

So on my page I want to display every tag, directly followed by the amount of documents that are filed under that tag so if 2 documents are under 'java' it's say java (2)

SELECT
    tags.id,
    naamnl,
    COUNT(*) AS 'count'
FROM
    tags, 
    bibitems, 
    taglinks
WHERE
    bibitems.id=taglinks.item_id 
    AND tag_id=tags.id
GROUP BY
    naamnl

This works well when there are documents filed under a certain tag. But when there's a tag that's unused so far, it won't pop up.

I want it to display java (0) in that case

Upvotes: 1

Views: 112

Answers (3)

MK.
MK.

Reputation: 34597

Read on the difference between INNER and OUTER joins. 

SELECT tag.id, naamnl, COUNT(taglink.tag_id) AS 'count' 
FROM tag
LEFT OUTER JOIN taglink ON taglink.tag_id=tag.id
LEFT OUTER JOIN bibitem ON bibitem.id=taglink.item_id 
GROUP BY tag.id, naamnl

Upvotes: 1

Jason
Jason

Reputation: 2887

Something like thisshould do it. (It is difficult since you did not provide the full table schemas.)

SELECT
   i.naamnl,
   COALESCE(t.Count, 0)      AS Count
FROM
   bibitems i
LEFT JOIN
   (
      SELECT
         tag_id,
         COUNT(*) AS Count
      FROM
         taglinks
      GROUP BY
         item_id
   ) t
 ON
    t.tag_id = i.tag_id

Upvotes: 0

ruakh
ruakh

Reputation: 183602

SELECT tags.id,
       tags.naamnl,
       COUNT(bibitems.id) AS 'count'
  FROM tags
  LEFT
 OUTER
  JOIN taglinks
    ON taglinks.tag_id = tags.id
  LEFT
 OUTER
  JOIN bibitems
    ON bibitems.id = taglinks.item_id
 GROUP
    BY tags.id,
       tags.naamnl
;

or

SELECT tags.id,
       tags.naamnl,
       ( SELECT COUNT(*)
           FROM bibitems
          WHERE bibitems.id IN
                 ( SELECT taglinks.item_id
                     FROM taglinks
                    WHERE taglinks.tag_id = tags.id
                 )
       )
  FROM tags
 GROUP
    BY tags.id,
       tags.naamnl
;

(Take your pick.)

Upvotes: 2

Related Questions