Jump to content

Recommended Posts

Hi

 

I need to make a query to get all data, this is the one

DECLARE @ReportYear INT
DECLARE @ReportMonth INT
DECLARE @EmployeeId INT
set @ReportYear = 2014
set @ReportMonth = 12
select distinct(pc.Name) as Categoria,
DATEPART(YEAR, soh.OrderDate) as [ano],
sum((det.UnitPrice-det.UnitPriceDiscount)*det.OrderQty) as total,
sum(soh.Freight) as transporte,
SUM(det.LineTotal) as vendas
from Sales.SalesOrderHeader as soh
inner join Sales.SalesPerson as sp
on sp.BusinessEntityID = soh.SalesPersonID
inner join Sales.SalesOrderDetail as det
on soh.SalesOrderID = det.SalesOrderID
inner join HumanResources.Employee as e
on soh.SalesPersonID = e.BusinessEntityID
inner join Person.Person as per
on per.BusinessEntityID = sp.BusinessEntityID
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
where ((DATEPART(YEAR, soh.OrderDate) = @ReportYear) or (DATEPART(YEAR, soh.OrderDate) <= @ReportYear))
group by
DATEPART(YEAR, soh.OrderDate),soh.Freight, pc.Name
order by DATEPART(YEAR, soh.OrderDate);

But the data comes with all repeated

 

I want to have just one Acessories/Clothes/Bicicle, etc and one time 2011/2012 etc...

 

Any help?

post-174418-0-51663600-1491306737_thumb.png

Link to post
Share on other sites

DISTINCT + GROUP BY don't make sense together.

 

Get rid of the DISTINCT and the GROUP BY should work properly. Note that you'll get one row per combination of year + soh.Freight + name combined so maybe you don't want Freight in there.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.