Some Useful SQL Things that i came across as Learning SQL

Restrict the Aggregate result using Having Rather than where.

SELECT EMAIL , COUNT(EMAIL) AS C 
FROM Person 
WHERE C > 1 
-- This is a bad idea , better use having 
SELECT EMAIL , COUNT(EMAIL) AS C 
FROM Person 
GROUP BY Email 
HAVING  C > 1 ; 

Some times when you want to write a query to only get the certain columns using the subquries but the number of items returned by the subquries must match the number of columns you are comparing it with.

SELECT EMAIL 
FROM Person
WHERE EMAIL IN 
(
SELECT NAME , COUNT(NAME)  C
GROUP BY NAME 
HAVING C>1
) 
-- This will produce and error as the where Clause expect 1 value but subquries
-- returns 2 
-- THe workaround to this is using SELECT FROM Instead of WHERE 
SELECT EMAIL 
FROM 
(SELECT EMAIL , COUNT(EMAIL) AS C 
FROM Person 
GROUP BY Email 
HAVING  C > 1 )A ;

Joining the Tables with One to many relationship Columns

Little bit on the data on the table

Users table

Transactions Table

Output if we dont group by the one to many relationship table (Users.account(1)-> (M) Transactions.account)

Well what has happened here is that it used sum for every amount which is not what we really want , so we need to group by the Joining table

More about this in this :

Efficient way to join two tables with one to many relationship