CODEWITHSUNDEEP
phpmysql
Fuxi
Fuxi

Reputation: 7599

mysql left join count always returns 1

here's my data structure:

categories 
id   name
-------------------
1    category1
2    category2
3    category3


items
id    name    cat
-------------------
1     item1   1
2     item2   1
3     item3   1
4     item4   2

desired output:

cat   category    total_items
-----------------------------------
1     category1   3
2     category2   1
3     category3   0

i tried the following query:

select categories.id as cat, 
    categories.name as category, 
    count(*) AS total_items from categories 
    left join items on categories.id = items.cat

and it will always return 1 for category 3 .. any ideas what's wrong?

Upvotes: 1

Views: 161

Answers (2)

surabhivin
surabhivin

Reputation: 139

Try it...

select categories.id as cat, 
    categories.name as category, 
    count(*) AS total_items from items
    left join categories on items.cat=categories.id

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

select categories.id as cat, categories.name as category, 
       count(items.cat) AS total_items 
from categories 
left join items on categories.id = items.cat

The problem with your query is that COUNT(*) counts on a row basis, including rows with NULL-valued fields from items table.

Using count(items.cat) instead, leaves NULL-valued fields out.

Upvotes: 5

Related Questions