CODEWITHSUNDEEP
phpmysqlcount
darioml
darioml

Reputation: 108

No MySQL result until WHERE specified

I've been using simple MySQL commands for years, and wanted to get into more complicated ones. I've taken the task to rewrite some of my old code, but I can't see what's happening here.

I'm trying to merge two tables into one reults, with the second table only providing a "count".

TABLE customers:
customerID, name, boxID
TABLE codes:
boxID, code, retrieved

What I want is exactly what I get from a SELECT * FROM customer, but I want an extra column which has the count() for all codes in the codes table where the boxID is the same.

This is my current query;

SELECT customers.*, count(codes.code) as codesused 
FROM customers 
INNER JOIN codes 
ON customers.boxID = codes.boxID
WHERE codes.retrieved = 1

Which results in NULL until I add a "WHERE customers.customerID = 'x'". Would anyone be able to explain why I can't get what I want from the above code?

Upvotes: 0

Views: 85

Answers (1)

Idrees Khan
Idrees Khan

Reputation: 7752

When u combine aggregate function with other fields, You have to use group by clause. You can make your query like this;

SELECT customerID, name, boxID, count(codes.code) as codesused 
FROM customers 
INNER JOIN codes ON customers.boxID = codes.boxID 
GROUP BY customerID, name, boxID, codesused
HAVING codes.retrieved = 1

And you can't use where clause with group by, so you have to use HAVING

Upvotes: 5

Related Questions