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

Solution:

Query

SELECT State, CustomerName
FROM
(
SELECT  CustomerName, COUNT(*) 
FROM CUSTOMER_T
GROUP BY State
)
WHERE COUNT > 1

-------------------------------------------------------------------------

Explanation:

Here using nested query the query is written.

By Grouping based on the states, all the available customers on each states can be listed.

In that taking the count of each customers the solution is found.

-------------------------------------------------------------------------

Answer 2

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

.​

​