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

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 :

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql-exercises/sql-formatting-output-exercise-10.php