Sql query -Help

[gmc1103]

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?

[requinix]

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.