gmc1103 Posted April 4, 2017 Share Posted April 4, 2017 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? Link to comment Share on other sites More sharing options...
requinix Posted April 4, 2017 Share Posted April 4, 2017 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 comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.