Pacific-Design.com

    
Home Index

1. Databases

+ Convert

+ Create Table

+ Date

+ Delta

+ Injection

+ Memory

+ MySQL

+ ON DUPLICATE KEY

+ OpenGIS

+ Oracle

+ Partition

+ Query Optimization

+ Redis

+ Row Number

+ SQL Case

+ Temporary Table

+ Update

+ Validate Domains

+ XPath

Databases /

Aggregate Functions in MySQL

AVG()           Return the average value of the argument
BIT_AND()       Return bitwise and
BIT_OR()        Return bitwise or
BIT_XOR()       Return bitwise xor
COUNT(DISTINCT) Return the count of a number of different values
COUNT()         Return a count of the number of rows returned
GROUP_CONCAT()  Return a concatenated string
MAX()           Return the maximum value
MIN()           Return the minimum value
STD()           Return the population standard deviation
STDDEV_POP()    Return the population standard deviation
STDDEV_SAMP()   Return the sample standard deviation
STDDEV()        Return the population standard deviation
SUM()           Return the sum
VAR_POP()       Return the population standard variance
VAR_SAMP()      Return the sample variance
VARIANCE()      Return the population standard variance 


-- Aggregation of top IP Addresses

SELECT ip, count(ip) total
FROM keywords_nootrino
WHERE ip like "192%" -- limit the rows
GROUP BY ip
HAVING total > 100   -- limit the groups
ORDER BY total DESC

Result

ip              total
180.179.201     82
174.142.104     22
148.251.41      16
192.99.19       13

SQL - INNER JOIN

SELECT a.order_id, a.order_date, b.customer_id, b.customer_name
FROM Orders a
JOIN Customer b 
ON a.customer_id=b.customer_id;

SQL - LEFT JOIN

SQL will return all customers, and any orders they have placed
SELECT a.customer_name, b.order_id
FROM Customer a
LEFT JOIN Orders b
ON a.customer_id=b.customer_id
ORDER BY a.customer_name;

SQL - RIGHT JOIN

SQL returns all customers, and any orders they have placed
SELECT a.order_id, b.customer_name
FROM Orders a
RIGHT JOIN Customer b
ON a.customer_id=b.customer_id
ORDER BY a.customer_name;

SQL - UNION

SQL returns unique cities from Customers and Suppliers in Germany.
SELECT city FROM Customer WHERE country='Germany'
UNION
SELECT city FROM Supplier WHERE country='Germany'
ORDER BY city;

SQL - UNION ALL

SQL returns all and duplicate cities from Customers and Suppliers in Germany.
SELECT city FROM Customer WHERE country='Germany'
UNION ALL
SELECT city FROM Supplier WHERE country='Germany'
ORDER BY city;

SQL - Find Duplicate Emails

SELECT email
FROM student
GROUP BY email
HAVING COUNT(email) > 1

SQL - SELF JOIN

To query the data and get information for both people in one row
SELECT e1.EmployeeID, 
    e1.FirstName, 
    e1.LastName,
    e1.SupervisorID, 
    e2.FirstName as SupervisorFirstName, 
    e2.LastName as SupervisorLastName
FROM Employee e1
LEFT OUTER JOIN Employee e2 on e1.SupervisorID = e2.EmployeeID