Question

In the movies database and actor table, label all values in the last_name field that start with a, b or c as "A,B,C start" or "Other" and name it LastName, and do the same with all the values in the first_name field and name it FirstName. Group by FirstName and LastName, and return the count as total but only return grouping where the FirstName and the Lastname are equal. Order by total desc.

Answer 1

SELECT
     CASE
        WHEN LEFT(last_name, 1) = "a" THEN "A start"
        WHEN LEFT(last_name, 1) = "b" THEN "B start"
        WHEN LEFT(last_name, 1) = "c" THEN "C start"
        ELSE "Other"
      END AS LASTNAME,
      CASE
        WHEN LEFT(first_name, 1) = "a" THEN "A start"
        WHEN LEFT(first_name, 1) = "b" THEN "B start"
        WHEN LEFT(first_name, 1) = "c" THEN "C start"
        ELSE "Other"
      END AS FIRSTNAME,
      COUNT(1)
FROM
    movies.ActorsTable
WHERE LASTNAME = FIRSTNAME
GROUP BY FIRSTNAME,LASTNAME
ORDER BY DESC