CODEWITHSUNDEEP
sqlsql-like
Kendall O'Connor
Kendall O'Connor

Reputation: 1

Query with multiple string with like operator

I am new to SQL and I have this question on a homework assignment: "List the ids of customer representatives whose id is under 100 along with the total number of customers that each of these representatives serves in zip codes starting with 9 or 0 (combined)"

SO far I have this code: 

SELECT cust_rep, count(cust_nbr) as "CustomerCount"
FROM customer
WHERE cust_rep < 100
GROUP BY cust_rep
ORDER BY cust_rep;

But I do not know how to add in the zip code restriction. Thanks for your help!

Upvotes: 0

Views: 118

Answers (2)

Brad
Brad

Reputation: 3591

This will do what your query is doing in addition to makeusre sure the first chararacter of the zipcode is either a 0 or a 9

SELECT cust_rep, count(cust_nbr) as "CustomerCount"
FROM customer
WHERE cust_rep < 100
AND (
    // adding quotes to avoid implicit conversion
    LEFT(ZipCode, 1) = '0'
    OR LEFT(ZipCode, 1) = '9'
)

GROUP BY cust_rep
ORDER BY cust_rep;

Upvotes: 1

kays-chetoui
kays-chetoui

Reputation: 14

Try this.

SELECT CUST_REP, COUNT(*) as "CustomerCount"
FROM CUSTOMER 
WHERE cust_rep < 100 
   AND (zipCode like '0%' OR zipCode like '9%')
GROUP BY cust_rep
ORDER BY cust_rep;

Upvotes: 0

Related Questions