Getting SELECT statement to work

I have 2 tables set up like this

CREATE TABLE Customers (
customerID int NOT NULL AUTO_INCREMENT,
customerFirstName varchar(50) NOT NULL,
customerMiddleName varchar(50),
customerLastName varchar(50),
customerAddress varchar(50),
customerCity varchar(50),
customerState varchar(50),
customerZip int,
customerAreaCode int,
customerPhoneNo int,
customerDateOpened timestamp DEFAULT CURRENT_TIMESTAMP,
customerDateModified timestamp NULL,
accountID int,
PRIMARY KEY (customerID)
);

CREATE TABLE Accounts (
accountID int NOT NULL AUTO_INCREMENT,
customerID int,
PRIMARY KEY (accountID),
FOREIGN KEY (customerID) REFERENCES Customers (customerID)
);

Im trying to create a select statement to…
Display each customer number and the number of accounts they have. Sequence the results by the most number of accounts to the least number of accounts.

Heres what I have soo far

SELECT customerID AS customer, COUNT(accountID) AS number FROM Accounts WHERE accountID > 0 ORDER BY COUNT(accountID);

but its wrong, what a I missing:?

what’s missing is your GROUP BY clause

1 Like

ok changed the query to

SELECT customerID AS customer, COUNT(customerID) AS number FROM Accounts GROUP BY number

but it still shows an wrror, this makes sence to me as arent I counting all the same customers (customerID), then grouping them by the number?

oh, had a stupid logic error (im full of those) but this worked…

SELECT customerID AS customer, COUNT(customerID) AS number FROM Accounts GROUP BY customer ORDER BY number DESC

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.