Here are some of my favorite MySQL queries. We process tons of data and I've been digging sql for over a year now.

Count Duplicates in a table. This sample counts dups by email column.

select (count(*)) - ( count(distinct(Table1.email))) from Table1

Removing duplicate by email address. First we create a unique table and then drop the original table and rename the new table.

CREATE TABLE workTable2 AS
SELECT * FROM workTable WHERE 1 GROUP BY email; DROP TABLEworkTable;
RENAME TABLEworkTable workTable2;

Insert with subTotals. This will pull out all the Females whose first name occurs more than five times. Giving me a table of the most popular female first names and how many times they occur. Which is nice for greetings - Hello Patricia, you are one of 53 Patricias here at theBizzel and have the 8th most popular first name.

insert into fNamesCounts (name, gender, code) SELECT name,gender,COUNT(name) AS code FROM users WHERE gender = "f" GROUP BY name HAVING ( COUNT(name) > 5 );

Show Monthly Action - This query demonstrates how to view the current months statistics. Below shows us how many Standard Users logged in for the month.

SELECT COUNT(*), MONTH(inDate), YEAR(inDate)
FROM logins WHERE type = 'STNDUSR'
GROUP BY YEAR(inDate), MONTH(inDate)

Who's logged in more the 50 times. This one uses CONCAT to combine first and last names also.

SELECT usrKey, CONCAT(fname, ' ',lname),
COUNT(usrKey) AS NumOccurrences
FROM logins
GROUP BY usrKey
HAVING ( COUNT(usrKey) > 50 )

Next Time I'll post some more advanced samples using variables and some fancy math. If you've got some way cool queries please reply them to this post so I can check them out and you can share your SQL smarts. Happy SQLing!!!