SQL Exercise: Purchase amount and commission for the last order
SQL Formatting Output: Exercise-10 with Solution
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
Sample Solution:
SELECT ord_date, SUM(purch_amt),
SUM(purch_amt)*.15
FROM orders
GROUP BY ord_date
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:

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.
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join