CODEWITHSUNDEEP
phpmysqlselectjoin
Peter
Peter

Reputation: 228

MySQL - Two table query, with SUM

Question resolved

I have two tables, orders and customers, and I'd like to find all customers where orders is greater than 0.

I currently use two queries:

SELECT * FROM customers

and 

SELECT count(id) 
FROM orders 
WHERE customer='CUSTOMER_VALUE' 
AND siteid='siteid'

I'd like to turn this into one query, so it finds all customers where they've placed one or more orders in the store.

I tried the following, but it doesn't work: 

SELECT c.*, 
       COUNT(o.customer) AS numOrders 
FROM customers c, 
     orders o 
WHERE o.siteid= 'calico' 
AND o.customer=c.email

(it only gives one result, which is customer ID 1).

I only need to find the orders value, and a few values from the customers table.

The customer field in orders and the id field in customers are the same value (ie order 1 was placed by customer 5, customer id 5 is "John").

Is there any way to do this?

The current way works, but it would be greatly inefficient if there was to be a large amount of customers.

Upvotes: 0

Views: 554

Answers (6)

Derk Arts
Derk Arts

Reputation: 3460

Not sure if this would work, but you could try:

SELECT c.*, COUNT(o.customer) AS numOrders FROM customers c 
JOIN orders o on o.customer = c.id 
WHERE o.siteid= 'calico' AND o.customer=c.email AND numOrders > 0
GROUP BY c.id, c.email

Upvotes: 0

carpii
carpii

Reputation: 2001

The reason your second query is returning only 1 row, is because oyu dont have a GROUP BY. Unlike many SQL databases, MySQL does allow you to mix non-aggregated fields with aggregated ones, even though its technically not valid sql, and the results are unpredictable.

Try 

SELECT c.id, c.email, COUNT(o.customer) AS numOrders 
 FROM customers c 
  INNER JOIN orders o on (o.customer=c.email) 
  WHERE o.siteid= 'calico' 
 GROUP BY c.id, c.email

Upvotes: 3

Lion
Lion

Reputation: 19047

SELECT c.id, c.email, COUNT(o.customer) AS numOrders 
FROM customers c, orders o 
WHERE o.siteid= 'calico' AND o.customer=c.email
GROUP BY c.id, c.email

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65342

SELECT
  customers.*,
  count(*) as ordercount
FROM customers
INNER JOIN orders ON customers.id=orders.customer
WHERE orders.siteid= 'calico'
GROUP BY customers.id;

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

SELECT c.*
     , COUNT(*) AS numOrders 
FROM customers c
  JOIN orders o 
    ON o.customer = c.id
WHERE o.siteid = 'calico'
GROUP BY c.id

Upvotes: 0

juergen d
juergen d

Reputation: 204924

You can join the two tables like this:

SELECT c.* 
FROM customers c
INNER JOIN orders o ON o.customer = c.id

Using an Inner Join will only result the customers that have entries in the orders table.

Upvotes: 0

Related Questions