Jump to content


Photo

Sql query -Help


  • Please log in to reply
1 reply to this topic

#1 gmc1103

gmc1103
  • Members
  • PipPipPip
  • Advanced Member
  • 202 posts

Posted 04 April 2017 - 11:53 AM

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?

Attached Files



#2 requinix

requinix
  • Administrators
  • Forgotten Administrator
  • 8,488 posts
  • LocationWA

Posted 04 April 2017 - 01:11 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users