0

I'm trying to generate a report from data stored in my database but i'm having an error

Msg 8120, Level 16, State 1, Line 1
Column 'Production.ProductCategory.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

My query is the following

SELECT
    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
GROUP BY
    DATEPART(YEAR, soh.OrderDate)
ORDER BY
    DATEPART(YEAR, soh.OrderDate);

I want to have just one accessories/clothes/bicycle, etc and one time 2011/2012 etc...

2
  • 5
    just add pc.Name to the group by Commented Apr 19, 2017 at 15:45
  • 3
    Don't be scared to format your queries so they don't look like a wall of text. Commented Apr 19, 2017 at 15:47

2 Answers 2

3

change this line:

group by DATEPART(YEAR, soh.OrderDate)

to this

group by DATEPART(YEAR, soh.OrderDate), pc.Name
Sign up to request clarification or add additional context in comments.

Comments

1

add

GROUP BY pc.Name

to the bottom of your query

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.