w3resource

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 :

Syntax of make a report with order date in such a manner that, the latest order date will comes first along with the total purchase amount and total commission for that date

Visual presentation :

Result of a report with order date in such a manner that, the latest order date will comes first along with the total purchase amount and total commission for that date

Practice Online


Query Visualization:

Duration:

Query visualization of Latest order date (comes last) along with the total purchase amount and commission - Duration

Rows:

Query visualization of Latest order date (comes last) along with the total purchase amount and commission - Rows

Cost:

Query visualization of Latest order date (comes last) along with the total purchase amount and commission - 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.



Follow us on Facebook and Twitter for latest update.