MySQL Join to Count Transactions?

Question

I'm sorry if this has been posted before/similarly, I wasn't sure how to structure my question to find proper results.

Essentially, I have 2 tables. Customers and Orders. I'm trying to pull the customer name from the Customers table, customerName, and the amount of orders each customer has made. The Orders table has unique order ID's for every order placed, so my idea was to do a count on each order that belongs to each indiviual customer. Essentially, I want my query to output 2 columns, Customer name and Number of Orders. Then, I only want to display the customers that have greater than or equal to 5 orders.

These are the tables I'm working with.

CREATE TABLE Customers (
  customerNumber INTEGER NOT NULL,
  customerName VARCHAR(50) NOT NULL,
  salesRepEmployeeNumber INTEGER NULL,
  PRIMARY KEY (customerNumber),
  FOREIGN KEY (salesrepEmployeeNumber) references Employees(employeeNumber)
)ENGINE=innodb;

CREATE TABLE Orders (
  orderNumber int(11) NOT NULL,
  orderDate DATETIME NOT NULL,
  customerNumber INTEGER NOT NULL,
  PRIMARY KEY (orderNumber), 
  FOREIGN KEY (customerNumber) references Customers(customerNumber)
)ENGINE=innodb;

The query I was trying is:

SELECT
    c.customerName as 'Customer Name',
    count(o.orderNumber) as 'Number of Orders Made',
FROM Customers c
INNER JOIN Orders o;
    ON c.customerNumber = o.customerNumber
HAVING count(o.orderNumber) > '4';

However, this is just showing 1 single customer having what I believe is all orders attributed to them.

This is an example of the output I'm tring to obtain:

score 2 · Accepted Answer · answered Feb 25 '18 at 07:08

You need to aggregate by customer here, i.e. use GROUP BY:

SELECT
    c.customerName,
    COUNT(o.orderNumber) AS num_orders
FROM Customers c
INNER JOIN Orders o
    ON c.customerNumber = o.customerNumber
GROUP BY
    c.customerNumber
HAVING
    COUNT(*) >= 5;

One note here is that we aggregate by the customerNumber column, since this value is unique to each customer (the customerName may be duplicated across customers). However, we are free to select the customerName, because it is functionally determined by the customerNumber.

Thanks a lot. I'm new to working with MySQL and did not know of Group By. This will help me look into this more. — B.Vee, Feb 25 '18 at 07:22

cdaiga · Answer 2 · 2018-02-25T07:24:04.543

1

Try this:

SELECT
    c.customerName as 'Customer Name',
    count(o.orderNumber) as 'Number of Orders Made',
FROM Customers c
INNER JOIN Orders o;
    ON c.customerNumber = o.customerNumber
GROUP BY c.customerNumber, c.customerName
HAVING count(o.orderNumber) > 4;

See MySQL group by on mysqltutorial.org for insights.

edited Feb 25 '18 at 07:24

answered Feb 25 '18 at 07:04

cdaiga

4,861
3
22
42

1

Grouping by the customer name is not ideal, q.v. my answer for a better version of this query. – Tim Biegeleisen Feb 25 '18 at 07:22
Thanks a lot for the information. I am new to MySQL and was unaware of what Group By did. Thanks for the link, I'll definitely look into it more. – B.Vee Feb 25 '18 at 07:23
You're correct @TimBiegeleisen, but you will need the name too in the grouping. – cdaiga Feb 25 '18 at 07:26
@cdaiga No, you don't. Because `customerNumber` is the primary key of that table. You may select _any_ column from `Customers` if you group by the `customerNumber` primary key. – Tim Biegeleisen Feb 25 '18 at 07:26
Yeah I see, it's not necessary in MySQL, but in Oracle. Adding it will still produce the same result as yours. – cdaiga Feb 25 '18 at 07:28
1

@cdaiga It isn't needed in Oracle, either, as far I as know. The ANSI 92 standard allows selecting non aggregate columns with `GROUP BY` if they are _functionally dependent_ on the column in the `GROUP BY` clause. Because we `GROUP BY` the primary key column, therefore the values of any other column are uniquely determined, so it's OK (even on Oracle). You are right though, if grouping by a non primary key column, then it's wrong to select non aggregate columns. – Tim Biegeleisen Feb 25 '18 at 07:32
Thank you very much @TimBiegeleisen for the insight. I have learned something new today about group by. – cdaiga Feb 25 '18 at 07:34
1

I upvoted you, and check out Bill Karwin's answer [here](https://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by). I couldn't find an actual doc reference, but this is pretty close. – Tim Biegeleisen Feb 25 '18 at 07:36

MySQL Join to Count Transactions?

2 Answers2