Fixing a MySQL query that miscounts cases where count is 0 I have the following SQL code:

SELECT widgets.campaign_name, widgets.enddate, widgets.active,
       widgets.id_widget,COUNT(*) AS entries
  FROM widgets
  LEFT JOIN entries ON widgets.id_widget = entries.id_widget
  WHERE widgets.active IN ( 0 )
    AND id_user = 8
  GROUP BY widgets.campaign_name


It does exactly what I want to do beside one thing. If I have 0 entries on a campaign, it still shows 1 entry. I want to get rid of this problem and make it count exactly how many entries are. If 0, it should display 0.

The code above works fine for 1,2....n entries.
The problem is that outer joins produce NULL values in columns of the (right) table for rows of the left table that don't have a match. So you should use COUNT(entries.id_widget) which counts not null values in that column and not COUNT(*) which counts rows. 

Some more notes:


you can use aliases for tables to make the query more readable.
It's good to prefix all columns with the table name (i.e. widget.id_user) or alias (w.id_user). Otherwise the query will break if a column with the same name is added later in table entries. 
Unless the column campaign_name is the primary key of widgets or has a unique constraint, you should not use GROUP BY this way. All columns in the SELECT list should be either used in aggregate functions or appear in the GROUP BY list. MySQL does no such check and indeterminate results can be returned by queries that do not follow this rule.
If the columns (in SELECT) depend on the columns in GROUP BY (and that would be true in this case if campaign_name had a unique constraint), that's one exception that the results will be determinate and you can use this "feature" of MySQL. You should also check that ONLY_FULL_GROUP_BY setting is disabled if you want to use this.
For more details, read the MySQL Documentation: MySQL Extensions to GROUP BY and the discussions here: Why does MySQL add a feature that conflicts with SQL standards?.


The query becomes:

    SELECT w.campaign_name, w.enddate, w.active, w.id_widget,
           COUNT(e.id_widget) AS entries
    FROM widgets AS w
      LEFT JOIN entries AS e  ON w.id_widget = e.id_widget
    WHERE w.active IN ( 0 )
      AND w.id_user = 8
    GROUP BY w.id_widget, 
             w.campaign_name, 
             w.enddate, w.active ;