Member Avatar for ZombieKnight93

I'm not looking for others to do my homework, I just want help with getting an idea one what to do.

My class was given an assignment and one of the questions is:

Create a query that will show the order id, item id, price, sales tax and subtotal for all items in the order item table. Create column aliases to describe the sales tax and subtotal columns. Only include in your output those items that
sold for $20 or more. Order your output by order id. Your output should look like the following:

orderid Item ID Price Sales Tax Subtotal

1004 1004 32.50 1.9500 34.4500
1016 1020 47.95 2.8770 50.8270
1018 1018 47.50 2.8500 50.350

Here is what I got so far:

select *
,cast(round((price) * 0.06, 4)as numeric(6,4)) as [Sales Tax]
--,cast(price as varchar(10)) as [Subtotal]
,cast(price + [Subtotal] as varchar(20)) as [Subtotal]
from order_item
where price >= 20
order by orderid asc;

I don't know what I'm doing wrong. I got everything else correct, but I am stumped at the part with finding the subtotal, would someone be able to give me an idea on what I should do?

Edited by ZombieKnight93
  • 4 Contributors
  • 3 Replies
  • 442 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by daniellemacey

Dani AI

Generated

Common issues in the thread: referencing a column alias inside the same SELECT list and casting numeric values to text before doing arithmetic. ’s original attempt tried to add a nonexistent alias and also converted numbers to varchar, which prevents numeric addition. ’s question about grouping is important: the assignment expects a per-item subtotal (price + tax). If an order-level subtotal is needed, ’s SUM hint is the right approach. is correct conceptually — compute tax and add it to price (or multiply by 1 + tax rate) — but the implementation details matter.

A concise per-item pattern that avoids alias reuse and keeps numeric types:

DECLARE @taxRate DECIMAL(5,4) = 0.06;

SELECT oi.orderid,
       oi.itemid,
       CAST(oi.price AS DECIMAL(10,2))        AS Price,
       CAST(ROUND(oi.price * @taxRate, 4) AS DECIMAL(10,4)) AS [Sales Tax],
       CAST(ROUND(oi.price + (oi.price * @taxRate), 4) AS DECIMAL(10,4)) AS Subtotal
FROM order_item AS oi
WHERE oi.price >= 20.00
ORDER BY oi.orderid;

If the same computed value should be reused inside the SELECT, use CROSS APPLY, a derived table, or a CTE so the expression is written once and referenced cleanly (this also avoids rounding differences and repeated code).

For order-level totals (one row per order) use GROUP BY and SUM:

SELECT orderid,
       SUM(price)                                  AS TotalPrice,
       CAST(ROUND(SUM(price) * 0.06, 4) AS DECIMAL(12,4)) AS TotalSalesTax,
       CAST(ROUND(SUM(price) * 1.06, 4) AS DECIMAL(12,4)) AS OrderSubtotal
FROM order_item
GROUP BY orderid
HAVING SUM(price) >= 20.00
ORDER BY orderid;

Troubleshooting notes: don’t cast numeric values to varchar when planning to do arithmetic; aliases defined in the SELECT list cannot be referenced by other SELECT expressions (they can be used in ORDER BY); prefer DECIMAL/NUMERIC for money and be deliberate about rounding precision (assignment shows four decimals, but typical currency rounding is two).

Recommended Answers

All 3 Replies

Member Avatar for Taywin

How do you combine subtotal? Is it by item ID? order ID? In other words, what are you grouping in order to calculate subtotal???

Member Avatar for rch1231

Hello,

Ok first thing is stop using "SELECT * " when you don't need every field from the table and second what you can do is simply multiply the price times 1 plus the sales tax (i.e 1.06 in your code) because the equation can be simplified algebretically to:

Subtotal = (price * 1.00 ) + (price * 0.06)
Subtotal = (price * (1.00 + 0.06))
Subtotal = (price * 1.06)
Edited by rch1231
Member Avatar for daniellemacey

you can always SELECT SUM()

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.