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:?