SQL:DQL:Select Summarising

From virtualMV2015wiki
Jump to: navigation, search
 Home  MySQL <

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


Icon Objective.png

Objective

Your task on this page is to

  • the use of the SELECT statement to Order and group data.


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, p75-84)[1].

Icon References.png References

  1. 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)