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!
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;
0 to '0', it would be better to explicitly put a string there instead of hoping that the conversion will do what you want. Or it might be that it converts LEFT(ZipCode, 1) to a number - I can't remember which will take precedence: can you?
LEFT is a string function, so it cannot keep it as a number. You should provide at least a link to documentation which gives the conversion precedence rules to explain why you have not put quotes around 0 and 9. Or just put the quotes in. This is the second hint to avoid a downvote ;)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;
ZEROFILL.
LIKE?