CODEWITHSUNDEEP
mysqlsqldatabase
JimmyBanks
JimmyBanks

Reputation: 4728

Product Count from Database

I have a certain amount of products listed in a database in the table 'products_items'. A series of these are related to a category of products, whose names can be found in a separate table called 'products'. Im trying to determine the count of these by JOINing them with the categories using PRODUCTID

This is my query:

SELECT *, count(*) as itemcount 
FROM products 
    LEFT JOIN products_items
    USING(PRODUCTID) 
GROUP BY PRODUCTID 
ORDER BY `order` ASC, `PRODUCTID` ASC";

The problem here, is both categories with 0 and 1 items, are returning a value of 1. However the query works for any category which has <=2 items. Is there a way to correct this count to output the proper amount?

Upvotes: 0

Views: 308

Answers (2)

Puggan Se
Puggan Se

Reputation: 5846

If you specify what you wanted to count:

SELECT *, count(products_items.PRODUCTID) as itemcount 
FROM products 
    LEFT JOIN products_items
    USING(PRODUCTID) 
GROUP BY PRODUCTID 
ORDER BY `order` ASC, `PRODUCTID` ASC";

by telling mysql you want to count a field in the products_items, its going the get a count of 0 if there is no products_items for that product. while count(*) gives 1 as there was 1 line in products combined whit 0 lines in products_items.

Upvotes: 1

Sapna Agrawal
Sapna Agrawal

Reputation: 117

SELECT , count() as itemcount FROM products p LEFT JOIN products_items pi on (PRODUCTID) where p.PRODUCTID = pi.PRODUCTID GROUP BY PRODUCTID ORDER BY order ASC, PRODUCTID ASC";

Upvotes: 0

Related Questions