SQL select number of customer orders by criteria

Question

I'm trying to get the number of customer orders from a customerOrders table where the gender is male from a customerDetails table. Could someone produce the query?

This is that i've attempted:

SELECT COUNT(customerOrders.orderid) 
FROM customerOrders WHERE customerDetails.gender = 1 
FROM customerDetails

customerDetails table:

name, customerID, gender    
John, 17,  1

customerOrders table:

orderID,customerID
3,  17

Possible duplicate of [Select from one table matching criteria in another?](https://stackoverflow.com/questions/5446778/select-from-one-table-matching-criteria-in-another) — Carlos, Sep 16 '19 at 18:50
You have to join your two tables, but you haven't provided enough detail for us to guess what that join might look like. — Eric Brandt, Sep 16 '19 at 18:52
I've added the tables. Do i need to join where customerID in both? — Dan, Sep 16 '19 at 19:00

score 0 · Accepted Answer · answered Sep 16 '19 at 19:01

That's a simple JOIN with aggregate function COUNT():

SELECT COUNT(*) 
FROM customerOrders co
INNER JOIN customerDetails ON cd.customerID = co.customerID AND cd.gender = 1

There are two conditions in the ON clause of the JOIN:

the first one matches records across tables based on the customerID
the second one filters on male customers

SQL select number of customer orders by criteria

1 Answers1