CODEWITHSUNDEEP
sql-server-2008
farrukh farukh
farrukh farukh

Reputation: 21

Different Items in single row to get the total amount of that order

I Have an order table in which i insert all the items that my customer placed in his order in single row.like

Table_ORDER ( Od_id is primary key auto incrementd)

Od_Id    Cust_Name  Quantity of 101(int)    Quantity of 102       Quantity of 103
-----     -------   --------------------       ---------------    --------------
1          John          5                          4                  7
2           Kim           4                          3                  2

Another Table of Price is like

Table_ Price 

Prod_ID      Price (money)
-------      ------
101           5.2
102           2.5
103           3.5

Now I want to get the total amount of specific order placed by customer. Problem is that if i use differnt rows for different item that Order id will be changed and if i use single row then How I calculate the total price as i can put jst single Prod_ID colum.

Kindly Guide me and send some solutions

Regards

Upvotes: 1

Views: 267

Answers (2)

Tim
Tim

Reputation: 28530

"Problem is that if i use differnt rows for different item that Order id will be changed". This is easily fixed if you change the design of your database by moving the ordered products and quantity to a separate table related to the main order table by a foreign key.

Here's a very simple example:

Three tables, defined as below:

Table Orders
------------
OrderID (Identity column)
CustomerName

Table OrderDetails
------------------
OrderID (this is the foreign key from the table Order)
ProductID (this is the foreign key from the Products table)
Quantity

Table Products
--------------
ProductID
Price

Now you can get the total for a given order by doing a query like this:

SELECT SUM(ISNULL(od.Quantity, 0) * p.Price)
FROM   Orders o
JOIN   OrderDetail od
       ON o.OrderID = od.OrderID
JOIN   Products p
       ON od.ProductID = p.ProductID
WHERE  OrderID = 1

For customer 1 (John), the result is 60.50. For customer 2 (Kim), the result is 35.30 (based on the data in your question).

This design has the benefit of allowing you to add products to your Products table without having to change the schema (columns) of your Orders table.

Again, this is a very simple example just to illustrate the concept.

Upvotes: 0

Vishy
Vishy

Reputation: 1362

I do see that the table design violates most of the design values starting with no foreign key between tables.

But the worst case solution for your problem is here:

select ( q101*price101.price+q102*price102.price) as 'Total Price' from
(select p.id, q101, price from `order`, price p where p.id=101) as price101,
(select p.id, q102, price from `order`, price p where p.id=102) as price102,
(select p.id, q103, price from `order`, price p where p.id=103) as price103

I am just trying to build tables to connect the two of your tables and then query based on that.

But it gets tedious as the number of products grow. I would really suggest to think of a design alternative.

NOTE: I have chosen column names like this: Quantity of 101 = q101

Upvotes: 1

Related Questions