Grouping Customer Data

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_idsalescust_namecust_fullnametrans_grn_widgets_salestrans_blu_widgets_saleswidget_sales
46166X-cel LtdMrs Xaria Xanda12500.0010800.0023300.00
45855Shenley Shovels LtdMr Simon Sixsmith9500.0012500.0022000.00
45836Quentin's Cabins LtdMr Quentin Quinn7500.0014400.0021900.00
46035Kensome & Kingston LtdMrs Karen Kensome7000.0012600.0019600.00
46174Yeovil Yards LtdMr Yusef Yorke8000.0010800.0018800.00
45825Peacock & Bradshaw LtdMr Peter Peacock7500.0010200.0017700.00
45714East Empire LtdMs Elaine East8500.007800.0016300.00
46184Zen Products LtdMr Zane Zellweger5500.008200.0013700.00
46013Irlington Industries LtdMr Ian Icke4000.009550.0013550.00
45724Frannock Manor PLCMr Frederick Franklin7500.004200.0011700.00
45673Acorn Traders LtdMrs Anthea Adams6200.005400.0011600.00
45703Dimbleby PLCMiss Diana Dimbleby5500.005700.0011200.00
46023Jeff Johnston LtdMr Jeff Johnston3500.007200.0010700.00
46122Thompson's Tents LtdMr Toby Thompson3000.005400.008400.00
45893Wilton's Fine Products LtdMr Will Wilton5300.001800.007100.00
45683Billingham's of Braithwaite LtdMr Bertie Billingham4500.001800.006300.00
45921Zimmerman's of Sawtry LtdMr Zak Zanetti1000.004800.005800.00
45872United Uniforms LtdMs Una Unsworth2500.003000.005500.00
46111Sally's Supplies LtdMrs Sally Smithers1500.003600.005100.00
46131Ursula Umbridge LtdMrs Ursula Umbridge1500.003600.005100.00
45841Rena's Supplies LtdMrs Rena Rimshaw1000.004000.005000.00
45731Gimley of Gosborton LtdMr George Gimley3000.001200.004200.00
46141Violet Vans LtdMrs Violet Vardy2000.001800.003800.00
45911Yaxley Pies LtdMiss Yasmine Yolander1000.002400.003400.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


Next: >> Grouping Staff Sales Data


Or use the above 'SQL Queries' dropdown menu to jump to any stage.