Wednesday 5 September 2018

Mysql - LEFT JOIN with COUNT

I have 2 database tables

  1. directory
  2. directory_category
They are linked by the category id (as cat_id & id) in both tables respectively

I want to list all the categories in directory_category and at the same time count how many records are found in directory for this category (using a single sql query)
I have tried
SELECT
      directory_category.id
      directory_category.category_name
      directory.cat_id
      count(directory) as total_records
      FROM directory_category
      LEFT JOIN directory
      ON directory_category.id = directory.cat_id

This query only produces one record and the total_records seems to be the sum of the entire directory table

SELECT
    directory_category.id,
    directory_category.category_name,
    directory.cat_id,
    COUNT(directory.id) AS total_records
FROM directory_category
LEFT JOIN directory ON directory_category.id = directory.cat_id
GROUP BY directory_category.id

0 comments:

Post a Comment