Jump to content
Sign in to follow this  
gmc1103

Sql query -Help

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

Share this post


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.

Share this post


Link to post
Share on other sites

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.

Sign in to follow this  

×
×
  • 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.