SQLTeam.com | Weblogs | Forums

AdventureWorks select query




I'm having a strange error regarding the query i'm trying to implemente since in Excel i have one value, in this query i have another value
Example in excel getting the row from query i have those values
653,75 *17579 = 11 492 295,86
But in my query i have
202023096827,0144 :rage:

This is my query

use AdventureWorks2014;

select pc.Name as Categoria,
DATEPART(YEAR, soh.OrderDate) as ano,
sum(det.OrderQty) as quantidade,
sum(det.UnitPrice) / sum(det.OrderQty) as MediaPrecoUnit,
sum(so.DiscountPct) as descontos,
((sum(det.OrderQty) * sum(det.UnitPrice)) - sum(so.DiscountPct)) as total,
sum(soh.Freight) as transporte,
sum(det.LineTotal) as vendas
from Sales.SalesOrderHeader as soh
inner join Sales.SalesOrderDetail as det
on soh.SalesOrderID = det.SalesOrderID
inner join Production.Product as p
on det.ProductID = p.ProductID
inner join Production.ProductSubcategory as ps
on p.ProductSubcategoryID = ps.ProductSubcategoryID
inner join Production.ProductCategory as pc
on ps.ProductCategoryID = pc.ProductCategoryID
inner join Sales.SpecialOfferProduct as sop
on p.ProductID = sop.ProductID
inner join Sales.SpecialOffer as so
on sop.SpecialOfferID = so.SpecialOfferID
group by
DATEPART(YEAR, soh.OrderDate),pc.Name
order by DATEPART(YEAR, soh.OrderDate),pc.Name ;

And the result are

All the value from total, transporte e vendas seems wrong to me but i don't know why..

any help?


You can get the total value as ;

(sum(det.OrderQty)*sum(det.UnitPrice) / sum(det.OrderQty)) - sum(so.DiscountPct) as total,



Thanks for your reply, yes it Works with total but i still have problema with my "transporte" (freight) column
I think the values are too high...

And finally i would like to have like a percentage between total and freight...i mean if i sell x and i spent y in freight what is the percentage involved...is there a way to get this??