SQL Exercise: Purchase amount and commission for the last order
Daily Totals with Commission Calculation
From the following table, write a SQL query to calculate the summation of purchase amount, total commission (15% for all salespeople) by each order date. Sort the result-set on order date. Return order date, summation of purchase amount and commission.
Sample table: orders
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample Solution:
-- This query selects specific columns ('ord_date', the sum of 'purch_amt', and the calculated value of 15% of the sum of 'purch_amt') from the 'orders' table.
-- It groups the result set by 'ord_date', sums the 'purch_amt' for each group, and calculates 15% of the sum of 'purch_amt'.
-- Finally, it orders the result set by 'ord_date' in ascending order.
SELECT ord_date, SUM(purch_amt), SUM(purch_amt) * 0.15
-- Specifies the table from which to retrieve the data (in this case, 'orders').
FROM orders
-- Groups the result set by 'ord_date', summing the 'purch_amt' for each group.
GROUP BY ord_date
-- Orders the result set by 'ord_date' in ascending order.
ORDER BY ord_date;
Output of the Query:
ord_date sum ?column? 2012-04-25 3045.60 456.8400 2012-06-27 250.45 37.5675 2012-07-27 2400.60 360.0900 2012-08-17 185.79 27.8685 2012-09-10 6979.15 1046.8725 2012-10-05 215.76 32.3640 2012-10-10 4463.83 669.5745
Code Explanation:
The said query in SQL that retrieves the order date, sum of purchase amounts, and sum of purchase amounts multiplied by 0.15 (15%) from the 'orders' table. The result set is grouped by the "ord_date" column and ordered by the "ord_date" in ascending order.
This query returns a total purchase amount as well as a calculated column that shows 15% of the total purchase amount, organized according to when the order was placed.
Explanation :
Visual presentation :
Go to:
PREV :
Customer Unique Orders and Maximum Purchase.
NEXT : SQL Query on Multiple Tables Exercises Home
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
For more Practice: Solve these Related Problems:
- Write a SQL query to group orders by ord_date, summing purch_amt and calculating total commission as 15% of the sum, then order the results by ord_date ascending.
- Write a SQL query to retrieve ord_date, total purchase amount, and computed commission (15% of the total) for each day, filtering out dates where the sum is below 100.
- Write a SQL query to select ord_date, the sum of purch_amt, and total commission (sum(purch_amt)*0.15) for each day, including only dates with at least two orders.
- Write a SQL query to calculate daily totals for purch_amt and commission (15% of total), and add a computed column showing the difference between total purchase and commission, sorting by ord_date.
- Write a SQL query to output ord_date, summation of purch_amt, and commission (15% of the summation) for each order date, filtering out records where the commission is not a whole number.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.