SQL:DQL:Select Summarising
From virtualMV2015wiki
Home MySQL < 
Table of contents 

SQL Theorys: Using the SELECT statement to summarise data
A very useful feature of SQL is the ability to summarise data. This allows you to answer questions such as
 List all the areas people live in?
 How many Males and Females are there in a set of data?
 How many people live in a certain area?
 What is the total amount spent in different regions?
To Answer the questions we can modify the SELECT statement using
 DISTINCT (To list remove any duplicate rows)
 COUNT, AVG to do mathematical calculations such as add up values, find the average
 GROUP plus COUNT to add up something within each group
ObjectiveYour task on this page is to

Examples
To find out Where your clients come from.
SELECT DISTINCT ClTownCity FROM tblClient ORDER BY ClTownCity;
To find out how many clients you have.
SELECT COUNT (*) FROM tblClient;
To find out how many doctors you have as clients.
SELECT COUNT (*) FROM tblClient WHERE ClSalute = 'Dr';
How many clients you have For each Salute type (e.g. Mrs, Mr,Ms,Dr).
SELECT ClSalute, COUNT (*) AS Count FROM tblClient GROUP BY ClSalute;
How many transactions happened in a each year.
SELECT YEAR(TrnDate) AS 'Transaction Type', COUNT (*) AS Count FROM tblTran GROUP BY Year ORDER BY Year;
Some statistics for account transactions.
SELECT TrnType AS 'Transaction Type', MIN(*) AS Minimum, MAX(*) AS Maximum, MAX(*)MIN(*) AS Range, SUM(*) AS Total, AVG(S*) AS Average, Count(*) AS Count FROM tblTran GROUP BY TrnType;
This is just a start, to find out more refer to DuBois (2009, p7584)^{[1]}.
References
 ↑ DuBois, P. (2009) MySQL. Developer's Library. Fourth Ed. Pearson
SQL:DQL:Select Summarising. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 12, 2017, from http://www.virtualmv.com/wiki/index.php?title=SQL%3ADQL%3ASelect_Summarising (zotero)