The purpose of this set of web pages is to demonstrate knowledge of building Microsoft Excel based solutions, data structure design and live querying on data tables (MySQL and MS SQL).
Using a two table inner join with field concatenation and grouping, data can be generated showing staff sales. It is necessary to perform a sum totalling the sales of green and blue widgets and also to perform a count on the individual sales to produce a useful report.
This data would be important to a sales team manager to assess performance. The data is sorted by widget sales in ascending order so that the highest sales are listed first.
Live results from the database are below:
| staff_name | sales | trans_grn_widgets_sales | trans_blu_widgets_sales | widget_sales |
|---|---|---|---|---|
| Mr Charles Chuzzlewit | 12 | 22800.00 | 21600.00 | 44400.00 |
| Mr Billy Beaumont | 12 | 16200.00 | 22550.00 | 38750.00 |
| Miss Deirdrie Dawson | 10 | 18000.00 | 19400.00 | 37400.00 |
| Mrs Francesca Faversham | 9 | 17000.00 | 18600.00 | 35600.00 |
| Miss Gina Ginola | 8 | 14500.00 | 18000.00 | 32500.00 |
| Mrs Anthea Allen | 9 | 13500.00 | 17200.00 | 30700.00 |
| Mr Harry Harrison | 6 | 7500.00 | 19200.00 | 26700.00 |
| Mr Ernie Ecclestone | 6 | 9500.00 | 16200.00 | 25700.00 |
The above example shows output from data recorded from 1st January 2017 to the end of March.
MySQL Code
SELECT CONCAT(s.staff_salutation, ' ',s.staff_firstname, ' ', s.staff_surname) AS staffname,
COUNT(t.trans_cust_id) AS sales,
SUM(t.trans_grn_widgets_sales), SUM(t.trans_blu_widgets_sales),
SUM(t.trans_blu_widgets_sales + t.trans_grn_widgets_sales) AS widget_sales
FROM transactions AS t
INNER JOIN staff AS s
ON t.trans_staff_id = s.staff_id
GROUP BY s.staff_id
ORDER BY widget_sales DESC
T-SQL (MS SQL)
SELECT s.staff_salutation + ' ' + s.staff_firstname + ' ' + s.staff_surname AS staffname,
COUNT(t.trans_cust_id) AS sales,
SUM(t.trans_grn_widgets_sales) AS trans_grn_widgets_sales,
SUM(t.trans_blu_widgets_sales) AS trans_blu_widgets_sales,
SUM(t.trans_blu_widgets_sales + t.trans_grn_widgets_sales) AS widget_sales
FROM transactions t
INNER JOIN staff s
ON t.trans_staff_id = s.staff_id
GROUP BY s.staff_id, s.staff_surname, s.staff_firstname,s.staff_salutation
ORDER BY widget_sales DESC
Data below is filtered by a date range of 1st February 2017 to the end of February:
| staff_name | sales | trans_grn_widgets_sales | trans_blu_widgets_sales | widget_sales |
|---|---|---|---|---|
| Mr Charles Chuzzlewit | 6 | 9500.00 | 16800.00 | 26300.00 |
| Mr Billy Beaumont | 6 | 10000.00 | 13750.00 | 23750.00 |
| Mr Harry Harrison | 4 | 5000.00 | 14400.00 | 19400.00 |
| Mrs Anthea Allen | 4 | 7000.00 | 9400.00 | 16400.00 |
| Mr Ernie Ecclestone | 3 | 5000.00 | 9600.00 | 14600.00 |
| Miss Gina Ginola | 3 | 4500.00 | 6600.00 | 11100.00 |
| Mrs Francesca Faversham | 3 | 5500.00 | 5400.00 | 10900.00 |
| Miss Deirdrie Dawson | 3 | 4000.00 | 6600.00 | 10600.00 |
MySQL Code
SELECT CONCAT(s.staff_salutation, ' ',s.staff_firstname, ' ', s.staff_surname) AS staffname,
COUNT(t.trans_cust_id) AS sales,
SUM(t.trans_grn_widgets_sales), SUM(t.trans_blu_widgets_sales),
SUM(t.trans_blu_widgets_sales + t.trans_grn_widgets_sales) AS widget_sales
FROM transactions AS t
INNER JOIN staff AS s
ON t.trans_staff_id = s.staff_id
WHERE (trans_date BETWEEN '2017-02-01 00:00:00' AND '2017-02-28 23:59:59')
GROUP BY s.staff_id
ORDER BY widget_sales DESC
T-SQL (MS-SQL)
SELECT s.staff_salutation + ' ' + s.staff_firstname + ' ' + s.staff_surname AS staffname,
COUNT(t.trans_cust_id) AS sales,
SUM(t.trans_grn_widgets_sales) AS trans_grn_widgets_sales,
SUM(t.trans_blu_widgets_sales) AS trans_blu_widgets_sales,
SUM(t.trans_blu_widgets_sales + t.trans_grn_widgets_sales) AS widget_sales
FROM transactions t
INNER JOIN staff s
ON t.trans_staff_id = s.staff_id
WHERE (trans_date BETWEEN '2017-02-01' AND '2017-02-28')
GROUP BY s.staff_id, s.staff_surname, s.staff_firstname,s.staff_salutation
ORDER BY widget_sales DESC
We can also set this query so that it only returns the results for the top three sales staff for the period of February 2017, based on widget sales.
This is achieved with a LIMIT clause in MySQL or by specifying a TOP value in T-SQL:
| staff_name | sales | trans_grn_widgets_sales | trans_blu_widgets_sales | widget_sales |
|---|---|---|---|---|
| Mr Charles Chuzzlewit | 6 | 9500.00 | 16800.00 | 26300.00 |
| Mr Billy Beaumont | 6 | 10000.00 | 13750.00 | 23750.00 |
| Mr Harry Harrison | 4 | 5000.00 | 14400.00 | 19400.00 |
Click the button below to advance to the next stage
Or use the above 'SQL Queries' dropdown menu to jump to any stage.