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 :
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.
Previous SQL Exercise: Find largest number of orders booked by the customer.
Next SQL Exercise: SQL Query on Multiple Tables Exercises Home
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.