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).
Here the data has been grouped together to show widget sales per customer. In this instance it was necessary to drop the salespersons names, as this output is not suitable in a grouped list, as there could be multiple staff names in this data. Therefore only a two table inner join is appropriate.
To improve reporting two extra fields and calculations were created. 'sales' to show the number of sales made to the company and 'widget_sales' to total up the sales value of both green and blue widgets.
The output is ordered by widget_sales (descending) which is now the sum of both green and blue widget sales, so that the largest sales appear at the top of the data.
Utilising the grouping functions, we can now see how many sales were made to the customer and the total value of the sales. The previous example only showed the individual sales, in transaction order.
Live results from the database are below:
| trans_cust_id | sales | cust_name | cust_fullname | trans_grn_widgets_sales | trans_blu_widgets_sales | widget_sales |
|---|---|---|---|---|---|---|
| 4616 | 6 | X-cel Ltd | Mrs Xaria Xanda | 12500.00 | 10800.00 | 23300.00 |
| 4585 | 5 | Shenley Shovels Ltd | Mr Simon Sixsmith | 9500.00 | 12500.00 | 22000.00 |
| 4583 | 6 | Quentin's Cabins Ltd | Mr Quentin Quinn | 7500.00 | 14400.00 | 21900.00 |
| 4603 | 5 | Kensome & Kingston Ltd | Mrs Karen Kensome | 7000.00 | 12600.00 | 19600.00 |
| 4617 | 4 | Yeovil Yards Ltd | Mr Yusef Yorke | 8000.00 | 10800.00 | 18800.00 |
| 4582 | 5 | Peacock & Bradshaw Ltd | Mr Peter Peacock | 7500.00 | 10200.00 | 17700.00 |
| 4571 | 4 | East Empire Ltd | Ms Elaine East | 8500.00 | 7800.00 | 16300.00 |
| 4618 | 4 | Zen Products Ltd | Mr Zane Zellweger | 5500.00 | 8200.00 | 13700.00 |
| 4601 | 3 | Irlington Industries Ltd | Mr Ian Icke | 4000.00 | 9550.00 | 13550.00 |
| 4572 | 4 | Frannock Manor PLC | Mr Frederick Franklin | 7500.00 | 4200.00 | 11700.00 |
| 4567 | 3 | Acorn Traders Ltd | Mrs Anthea Adams | 6200.00 | 5400.00 | 11600.00 |
| 4570 | 3 | Dimbleby PLC | Miss Diana Dimbleby | 5500.00 | 5700.00 | 11200.00 |
| 4602 | 3 | Jeff Johnston Ltd | Mr Jeff Johnston | 3500.00 | 7200.00 | 10700.00 |
| 4612 | 2 | Thompson's Tents Ltd | Mr Toby Thompson | 3000.00 | 5400.00 | 8400.00 |
| 4589 | 3 | Wilton's Fine Products Ltd | Mr Will Wilton | 5300.00 | 1800.00 | 7100.00 |
| 4568 | 3 | Billingham's of Braithwaite Ltd | Mr Bertie Billingham | 4500.00 | 1800.00 | 6300.00 |
| 4592 | 1 | Zimmerman's of Sawtry Ltd | Mr Zak Zanetti | 1000.00 | 4800.00 | 5800.00 |
| 4587 | 2 | United Uniforms Ltd | Ms Una Unsworth | 2500.00 | 3000.00 | 5500.00 |
| 4613 | 1 | Ursula Umbridge Ltd | Mrs Ursula Umbridge | 1500.00 | 3600.00 | 5100.00 |
| 4611 | 1 | Sally's Supplies Ltd | Mrs Sally Smithers | 1500.00 | 3600.00 | 5100.00 |
| 4584 | 1 | Rena's Supplies Ltd | Mrs Rena Rimshaw | 1000.00 | 4000.00 | 5000.00 |
| 4573 | 1 | Gimley of Gosborton Ltd | Mr George Gimley | 3000.00 | 1200.00 | 4200.00 |
| 4614 | 1 | Violet Vans Ltd | Mrs Violet Vardy | 2000.00 | 1800.00 | 3800.00 |
| 4591 | 1 | Yaxley Pies Ltd | Miss Yasmine Yolander | 1000.00 | 2400.00 | 3400.00 |
MySQL Code
SELECT t.trans_cust_id, COUNT(t.trans_cust_id) AS sales, c.cust_name,
CONCAT(c.cust_salutation, ' ',c.cust_firstname,' ',c.cust_surname) AS cust_fullname,
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 customer AS c
INNER JOIN transactions AS t
ON c.cust_id = t.trans_cust_id
GROUP BY t.trans_cust_id
ORDER BY widget_sales DESC
T-SQL (MS SQL)
SELECT t.trans_cust_id, COUNT(t.trans_cust_id) AS sales, c.cust_name,
c.cust_salutation + ' ' + c.cust_firstname + ' ' + c.cust_surname AS cust_fullname,
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 customer AS c
INNER JOIN transactions AS t
ON c.cust_id = t.trans_cust_id
GROUP BY t.trans_cust_id, c.cust_name, c.cust_surname, c.cust_firstname, c.cust_salutation
ORDER BY widget_sales DESC
Click the button below to advance to the next stage
Or use the above 'SQL Queries' dropdown menu to jump to any stage.