MySQL - Two table query, with SUM

Question

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.

Does order.customer store the customer.id or the customer.email ? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Dec 24, 2011 at 12:40

Shirish11 · Accepted Answer · 2011-12-24 13:25:17Z

3

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

edited Dec 24, 2011 at 13:25

Shirish11

1,6375 gold badges18 silver badges41 bronze badges

answered Dec 24, 2011 at 12:32

carpii

2,0114 gold badges21 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ypercubeᵀᴹ Over a year ago

When you group by the Primary Key, it's technically valid SQL (by the SQL-2003+ standards) - although MySQL does not really check that and it allows technically invalid queries as well.

juergen d · Accepted Answer · 2011-12-24 12:29:57Z

0

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.

answered Dec 24, 2011 at 12:29

juergen d

205k40 gold badges305 silver badges377 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-12-24 12:31:18Z

0

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

answered Dec 24, 2011 at 12:31

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Comments

Eugen Rieck · Accepted Answer · 2011-12-24 12:31:49Z

0

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

answered Dec 24, 2011 at 12:31

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

Comments

Lion · Accepted Answer · 2011-12-24 12:46:27Z

0

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

edited Dec 24, 2011 at 12:46

answered Dec 24, 2011 at 12:33

Lion

19.1k22 gold badges83 silver badges111 bronze badges

Comments

Shirish11 · Accepted Answer · 2011-12-24 14:12:58Z

0

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

edited Dec 24, 2011 at 14:12

Shirish11

1,6375 gold badges18 silver badges41 bronze badges

answered Dec 24, 2011 at 12:31

Derk Arts

3,4702 gold badges20 silver badges36 bronze badges

Collectives™ on Stack Overflow

MySQL - Two table query, with SUM

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related