Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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 ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.