Question

write a sql query that returns Which states have more than one customer? List the states and the number of customers for those states. The list must not include the states that do not have more than one customer.

CUSTOMER_T

CustomerName, Street, City, State, Zip,

Answer 1

SELECT State, COUNT(State) as no_customers
FROM CUSTOMER_T
group by State having count(State) > 1;

We will use group by a statement to group the customers by the state which they are living in. After grouping using having clause, we will check whether the count of state (Which are grouped) is more than 1 or not. If it's more than one we will print that state and the number of customers as Count(state). From the table, we know that each row belongs to one unique customer

.​

​

Customer Name Peter Stark Chris Hemsworth Flash Street City Munich London State Germany UK Spain spain Germany UK Spain Gothamm Zip 13209 WA1 1DP 28022 28023 12209 WA1 28024 12344 Spidy street Aveng ers street Captain street Barcelpna Jotunheim central street mid street Super street Gotham street Madrid Berlin Manchester Biabo Gothamm Super Ginl Batman After applying GroupBY (State) Customer Name Peter Flash Stark Street City Munich Berlin State Germany Germany UK UK Spain spain Spain Zip 13209 12209 WA1 1DP WA1 28022 28023 28024 Spidy street central street Avengers street London mid street Manchester Chris Hemsworth Super Ginl Captain street Barcelona Madrid Bibao otha Jotunheim Super street stree an After applying having count(State) > 1 State Germany UK Spain Count(State) 2 2