Examples of Group By Query

Table: persons
id
first_name
last_name
favorite_ride_id

Table: rides
id
name

Write a query that will return the names and votes for the top 10 most popular rides in order from most to least popular.

Note: to use group by, the attributes after the select keyword must appear in the GROUP BY clause or be used in an aggregate function.
 

SELECT rid.name, cnt.votes
FROM rides AS rid
 INNER JOIN
   (
     SELECT favorite_ride_id, count(1) AS votes
     FROM persons
     GROUP BY favorite_rid_id
   ) AS cnt
       ON cnt.favorite_ride_id = rid.id
ORDER BY cnt.votes DESC;

Remove Duplicate Tuples in Database Tables

You have a database table Emp with data as follows:

EmpId FirstName LastName
1 Bob Lync
2 Sarah John
3 Bob Lync
4 John Doe
5 Stanly Jeff
6 Sarah John

With a single SQL query, how will you cleanup the database (eliminate redundant data from above table)

Solution: using group by, and we can find the first appearance of the empid of each user.

DELETE FROM emp 
WHERE empid NOT IN 
(SELECT MIN(empid)
FROM emp 
GROUP BY firstName , lastName);