Reg. SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reg. SQL

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 05:26:48

I am having Orders Table and Products Table and I have to write a single query to get Orderid, Orderdate, Ordervalue(Rate*Qty-Discount)

Order table has Orderid,Orderdate,Prodid,Qty,Discount
Product table has Prodid,PName,Rate

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 05:37:38

Select o.Orderid, o.orderdate,p.rate *(o.Qty-o.Discount) as ordervalue From orders o
inner join Product p
on p.prodid = o.prodid

Chirag

asarak
Starting Member

36 Posts

Posted - 2006-08-09 : 05:38:14

select o.Orderid, o.Orderdate, (p.Rate*o.Qty-o.Discount) as ordervalue
from orders as o,products as p
where o.prod_id=p.prod_id

ASARAK

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 05:45:19

Hi Chirag But If I have have two products with same orderid then what changes should be done to ur query.

quote:
Originally posted by chiragkhabaria

Select o.Orderid, o.orderdate,p.rate *(o.Qty-o.Discount) as ordervalue From orders o
inner join Product p
on p.prodid = o.prodid

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 05:50:00

Hi But If I have have two products with same orderid then what changes should be done to ur query.

quote:
Originally posted by asarak

select o.Orderid, o.Orderdate, (p.Rate*o.Qty-o.Discount) as ordervalue
from orders as o,products as p
where o.prod_id=p.prod_id

ASARAK

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 06:26:37

will their orderdate also will be same???
if so then you can use the group by query for that matter.

or else it will be great if you can post some sample data, somone over here can surely help you with that

Chirag

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 06:36:04

Select o.[Order-id], o.orderdate,sum(p.rate * o.Qty-o.Discount) as ordervalue From order1 o
inner join Product1 p
on p.[product-id] = o.[prod-id] group by o.[Order-id],o.orderdate order by o.[Order-Id] asc

vmurali
Yak Posting Veteran

88 Posts

Posted - 2006-08-09 : 07:00:05

Chirag, I am having one more doubt how can we get the rowid for a particular row in db. becoz I have to write a query to get the below and above rows of a given row.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-09 : 07:07:04

Its better to get the row id, in the front end since there is no direct way to get the Rowid

The query goes like this, but this query really hit the performance.


 Select (Select Count(1) From Order1 o1 where o1.<PK> >= o.<PK>) As rowid,
o.[Order-id], o.orderdate,sum(p.rate * o.Qty-o.Discount) as ordervalue From order1 o
inner join Product1 p
on p.[product-id] = o.[prod-id] group by o.[Order-id],o.orderdate order by o.[Order-Id] asc

Replace PK by Primary key

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources