Issue with join

[Shweta]

Hi All,

I have three table in my database
1) products with two columns - product, price
2) region with two columns - SalesPerson, Region
3) transactions with five columns - date, SalesPerson, Product, Discount, Units

I want region wise net revenue. Calculation for revenue is : price*(1-discount)*Units

I am using below query for it. but it is not working. Kindly help me out on this.

select R.Region,(P.Price*(1-T.Discount)*T.Units) as [Net Revenue]
from region as R
inner join Transactions as T
on R.SalesRep = T.SalesRep
inner join Products as P
on p.Products = t.Product
group by r.Region

Thanks in advance!

Regards,
Shweta

 

[Joe4]

How is it not working? What is happening?

If you want total revenue by Region, I think you will need to use the SUM function on your calculation, i.e.

[COLOR=#333333]select R.Region,[/COLOR][COLOR=#ff0000][B]Sum[/B][/COLOR][COLOR=#333333](P.Price*(1-T.Discount)*T.Units) as [Net Revenue][/COLOR]

 

[Shweta]

Ohh...Thank you so much

 

[Joe4]

You are welcome!

Yes, with Aggregate Queries, for all the columns/calculations you are displaying, you are either Grouping them, or applying some aggregate function on them (SUM, COUNT, etc).