AdventureWorks Database: Calculate the percentage of tax on subtotal
SQL Query - AdventureWorks: Exercise-5 with Solution
5. From the following table write a query in SQL to return all rows from the salesorderheader table in Adventureworks database and calculate the percentage of tax on the subtotal have decided. Return salesorderid, customerid, orderdate, subtotal, percentage of tax column. Arranged the result set in descending order on subtotal.
Sample table: sales.salesorderheader
salesorderid|revisionnumber|orderdate |duedate |shipdate |status|onlineorderflag|purchaseordernumber|accountnumber |customerid|salespersonid|territoryid|billtoaddressid|shiptoaddressid|shipmethodid|creditcardid|creditcardapprovalcode|currencyrateid|subtotal |taxamt |freight |totaldue |comment|rowguid |modifieddate |
------------+--------------+-----------------------+-----------------------+-----------------------+------+---------------+-------------------+--------------+----------+-------------+-----------+---------------+---------------+------------+------------+----------------------+--------------+-----------+----------+---------+-----------+-------+------------------------------------+-----------------------+
43659| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO522145787 |10-4020-000676| 29825| 279| 5| 985| 985| 5| 16281|105041Vi84182 | | 20565.6206| 1971.5149| 616.0984| 23153.2339| |79b65321-39ca-4115-9cba-8fe0903e12e6|2011-06-07 00:00:00.000|
43660| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18850127500 |10-4020-000117| 29672| 279| 5| 921| 921| 5| 5618|115213Vi29411 | | 1294.2529| 124.2483| 38.8276| 1457.3288| |738dc42d-d03b-48a1-9822-f95a67ea7389|2011-06-07 00:00:00.000|
43661| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18473189620 |10-4020-000442| 29734| 282| 6| 517| 517| 5| 1346|85274Vi6854 | 4| 32726.4786| 3153.7696| 985.553| 36865.8012| |d91b9131-18a4-4a11-bc3a-90b6f53e9d74|2011-06-07 00:00:00.000|
43662| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18444174044 |10-4020-000227| 29994| 282| 6| 482| 482| 5| 10456|125295Vi53935 | 4| 28832.5289| 2775.1646| 867.2389| 32474.9324| |4a1ecfc0-cc3a-4740-b028-1c50bb48711c|2011-06-07 00:00:00.000|
43663| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO18009186470 |10-4020-000510| 29565| 276| 4| 1073| 1073| 5| 4322|45303Vi22691 | | 419.4589| 40.2681| 12.5838| 472.3108| |9b1e7a40-6ae0-4ad3-811c-a64951857c4b|2011-06-07 00:00:00.000|
43664| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16617121983 |10-4020-000397| 29898| 280| 1| 876| 876| 5| 806|95555Vi4081 | | 24432.6088| 2344.9921| 732.81| 27510.4109| |22a8a5da-8c22-42ad-9241-839489b6ef0d|2011-06-07 00:00:00.000|
43665| 8|2011-05-31 00:00:00.000|2011-06-12 00:00:00.000|2011-06-07 00:00:00.000| 5|false |PO16588191572 |10-4020-000146| 29580| 283| 1| 849| 849| 5| 15232|35568Vi78804 | | 14352.7713| 1375.9427| 429.9821| 16158.6961| |5602c304-853c-43d7-9e79-76e320d476cf|2011-06-07 00:00:00.000|
-- more --
Sample Solution:
-- Selecting specific columns (salesorderid, customerid, orderdate, subtotal) from the sales.salesorderheader table
SELECT salesorderid, customerid, orderdate, subtotal,
-- Calculating the tax percentage by multiplying taxamt by 100 and dividing by subtotal
(taxamt*100)/subtotal AS Tax_percent
-- Specifying the table from which the data will be retrieved, with "sales" being the schema name and "salesorderheader" being the table name.
FROM sales.salesorderheader
-- Ordering the result set by the 'subtotal' column in descending order
ORDER BY subtotal desc;
Explanation:
- The SELECT salesorderid, customerid, orderdate, subtotal, (taxamt*100)/subtotal AS Tax_percent statement specifies the columns to be retrieved from the "salesorderheader" table. It also calculates the tax percentage using the expression (taxamt*100)/subtotal, where taxamt is multiplied by 100 and divided by subtotal. The calculated tax percentage is aliased as "Tax_percent" for easier reference in the result set.
- FROM sales.salesorderheader specifies the table from which the data will be retrieved, with "sales" being the schema name and "salesorderheader" being the table name.
- The ORDER BY subtotal desc clause sorts the result set based on the "subtotal" column in descending order.
Sample Output:
salesorderid|customerid|orderdate |subtotal |tax_percent |
------------+----------+-----------------------+-----------+-------------------+
51131| 29641|2013-05-30 00:00:00.000|163930.3943|10.9488656308319512|
55282| 29641|2013-08-30 00:00:00.000|160378.3913|10.2805612815745958|
46616| 29614|2012-05-30 00:00:00.000|150837.4387| 9.9382830477616695|
46981| 30103|2012-06-30 00:00:00.000|147390.9328| 9.8971768635146327|
47395| 29701|2012-07-31 00:00:00.000|146154.5653| 9.8391246078989227|
47369| 29998|2012-07-31 00:00:00.000|140078.3959| 9.7785509407021986|
47355| 29957|2012-07-31 00:00:00.000| 129261.254| 9.7141627606366870|
51822| 29913|2013-06-30 00:00:00.000|128873.2206| 9.8028539530422816|
44518| 29624|2011-10-01 00:00:00.000|126198.3362| 9.7285313496866847|
57150| 29923|2013-09-30 00:00:00.000| 122285.724| 9.6171868762047809|
51858| 29940|2013-06-30 00:00:00.000|122284.4578|11.0640782511610400|
43875| 29624|2011-07-01 00:00:00.000|121761.9396| 9.7497652706576957|
46607| 29994|2012-05-30 00:00:00.000| 120182.185| 9.7784823932099421|
46660| 29646|2012-05-30 00:00:00.000|117274.3453|10.0397911153378232|
...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Retrieve only the rows for product.
Next: Create a list of unique jobtitles.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics