January 20, 2011 at 7:32 pm
I have the following database structure and I need a SQL query to retrieve a list of order ids and their totals (including shipping amount). And I need to only show records prior to June 1, 2002. Also, I need to sort the results so that the newest orders are first. I need to write this in standard SQL. Please help.
OrdersOrderItems
id
date
shipping_amount
order_status
customer_idid
order_id (Orders.id)
product_id
price
quantity
The output should look something like this:
+-----------+------------+
| OrderID | OrderTotal |
+-----------+------------+
| 13230 | $55.00 |
| 54455 | $40.00 |
| 59694 | $33.04 |
| 39495 | $21.05 |
| 59349 | $12.55 |
+-----------+------------+
January 20, 2011 at 8:01 pm
mailboxo71 (1/20/2011)
I have the following database structure and I need a SQL query to retrieve a list of order ids and their totals (including shipping amount). And I need to only show records prior to June 1, 2002. Also, I need to sort the results so that the newest orders are first. I need to write this in standard SQL. Please help.OrdersOrderItems
id
date
shipping_amount
order_status
customer_idid
order_id (Orders.id)
product_id
price
quantity
The output should look something like this:
+-----------+------------+
| OrderID | OrderTotal |
+-----------+------------+
| 13230 | $55.00 |
| 54455 | $40.00 |
| 59694 | $33.04 |
| 39495 | $21.05 |
| 59349 | $12.55 |
+-----------+------------+
This should help you.
Select Orderid, (quantity * price) + shipping_amount
From Orders
Group By orderid
Order by orderid
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply