my table is like this: supplier table(Snumber, sname, status, birthday, city)
I am trying to answer this question:
How many suppliers have distinct their first letter of their name (not repeated) and how many supplier cities have distinct the last letter of their cities for all the suppliers who were born after 1970?
I need to look at the first letter of every sname of the table and figure out if it is repeated in that name or not... i need to output how many names had a first letter that was not repeated in that same name.
EX: sname = gordon the first letter is a g but the g isnt repeated in the name so gordon would be counted.
EX: City london n is the last letter but the n happens 2 times in london so london would not be counted.
for the cities the same but the last letter instead... i used NOT LIKE but it still doesn't seem to work
What I have tried:
SELECT(SELECT COUNT(*)
FROM supplier
WHERE SUBSTRING(supplier.sname,1,1) NOT LIKE
CONCAT('%',SUBSTRING(supplier.sname,1,1))) AS supplier_Count,
(SELECT COUNT(*)
FROM supplier
WHERE SUBSTRING(supplier.city,LENGTH(supplier.city) - 1,1) NOT LIKE
CONCAT('\'',SUBSTRING(supplier.city,LENGTH(supplier.city) - 1,1),'%\'')) AS City_Count;