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

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

Pictorial 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.

SQL: Tips of the Day

MySQL Like multiple values

The (a,b,c) list only works with in. For like, you have to use or:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

Ref: https://bit.ly/3CIZPB5