Sql select group by error: field not contained in aggregate function or group by clause

Question

0.00/5 (No votes)

See more:

SQL

SELECT a.userName, productType, MAX(TotalSales) 
	FROM(
		SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
			FROM [SaleProduct] SP
				JOIN [Sale] S ON SP.SaleId = S.SaleId
					JOIN [User] U ON U.UserId = S.UserId
						GROUP BY SP.Type, U.UserName) AS a
		GROUP BY productType, a.userName
		)

gives me

Name| Product-Type | Sales
XYZ | Laptops| 999
ABC | Desktop| 888
ABC | Servers|777
XYZ | Servers|555

I want:

Name| Product Type|Sales
XYZ |Laptops| 999
ABC |Desktop| 888
ABC |Servers|777

If I only need to return the array above ( ABC having the most total sales of servers ), how should I write the query ?
The Query below works , expect that I still need to return "Name" from the outer query (but by joining Name and including it in the outer query , it also return XYZ with total sales of 555. How to do it ?

What I have tried:

SQL

SELECT productType, MAX(TotalSales) 
	FROM(
		SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
			FROM [SaleProduct] SP
				JOIN [Sale] S ON SP.SaleId = S.SaleId
					JOIN [User] U ON U.UserId = S.UserId
						GROUP BY SP.Type, U.Name) AS a
		GROUP BY productType
		)

the above query works expect I still need to return Name from the outer SELECT query
how to do this ?

Posted 10-Nov-18 9:04am

BikliWe Wiyao ALI

Updated 13-Nov-18 6:01am

v7

Add a Solution

Comments

Bryian Tan 10-Nov-18 18:29pm

What??? not clear what your question about and not enough information. Maybe you want to include the "my" column in your last query?

SELECT my, productType, MAX(TotalSales) 
	FROM(
		SELECT U.UserName AS my, SP.Type AS productType, SUM(SP.Price) AS TotalSales
			FROM [SaleProduct] SP
				JOIN [Sale] S ON SP.SaleId = S.SaleId
					JOIN [User] U ON U.UserId = S.UserId
						GROUP BY SP.Type, U.UserName) AS a
		GROUP BY my, productType

CHill60 13-Nov-18 8:44am

I think I have the solution you need but the servers are a little busy at the moment. I will try again later

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-11-10T09:10:00

Solution 1

See here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]

Posted 10-Nov-18 9:10am

OriginalGriff

Comments

BikliWe Wiyao ALI 10-Nov-18 16:29pm

The provided link does not outline my question. Can you give me a sample code from my question to resolve the issue

OriginalGriff 11-Nov-18 2:13am

Yes, it does - it doesn't give you the code you think you need, but it explains why you get the problem. Which means once you sort it out, you don;t need to ask again next time ...

kmoorevs · Answer 2 · 2018-11-10T09:47:00

Solution 2

Every field in your select statement that you are not performing an aggregate function on (Sum, Avg, Max, etc) must be listed in the Group By clause. Also, the Group By does not allow field aliases, you should use the actual field name there.

If you get your example working with the sub-query, all you need to do is reference the 'my' alias for your user in the outside query, then remember to group on it.

Posted 10-Nov-18 9:47am

kmoorevs

Comments

BikliWe Wiyao ALI 10-Nov-18 16:24pm

1."Every field in your select statement that you are not performing an aggregate function on (Sum, Avg, Max, etc) must be listed in the Group By clause", that I understood.
2."the Group By does not allow field aliases, you should use the actual field name there." by doing so, it gives me field not bounded error since( it is in outer query)
3."..all you need to do is reference the 'my' alias for your user in the outside query, then remember to group on it". Like I said grouping on "my" gives me more that I need to return as in the first array. Unless I am not getting you can you suggest an example code

kmoorevs 10-Nov-18 16:50pm

Sorry, I misread your queries originally. I was thrown off by the extra ) at the end of the outer query. It's not clear what you are trying to get as results. Top sales person per product type? (what about product type 555?)

CHill60 · Answer 3 · 2018-11-13T06:01:00

The other posters are picking up on your errors on the group by, I'm going to attempt to answer the underlying question.

I think what you are trying to do is just return the highest ranking UserName based on total sales by Product Type. The easiest way to do that is to use the ROW_NUMBER function with PARTITION BY - see ROW_NUMBER (Transact-SQL) | Microsoft Docs[^]

For example:

SQL

;with CTE AS
(
	SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
	, ROW_NUMBER() OVER (PARTITION BY sp.type ORDER BY SUM(SP.Price) DESC) as rn
	FROM #SaleProduct SP
	LEFT JOIN #Sale S ON SP.SaleId = S.SaleId
	LEFT JOIN #User U ON U.UserId = S.UserId
	GROUP BY SP.Type, U.Name
)
SELECT CTE.* FROM CTE WHERE rn = 1

I've chosen to use a Common Table Expression as I find them easier to follow - the equivalent using a sub-query would be

SQL

SELECT * FROM 
(
	SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
	, ROW_NUMBER() OVER (PARTITION BY sp.type ORDER BY SUM(SP.Price) DESC) as rn
	FROM #SaleProduct SP
	LEFT JOIN #Sale S ON SP.SaleId = S.SaleId
	LEFT JOIN #User U ON U.UserId = S.UserId
	GROUP BY SP.Type, U.Name
) as a
WHERE rn = 1

Points to note:
- I've used PARTITION BY to restart the numbering within each "group" of things (in this case product types). If I just run the inner query I get these results:

ABC	Desktop	888.00	1
XYZ	Laptop	999.00	1
ABC	Server	777.00	1
XYZ	Server	555.00	2

Notice that rn is showing who came in at number 1, number 2 etc.

This solution does not take into account ties (i.e. two salemen have exactly the same amount of sales for a specific product). Joint winners! To cater for that scenario you would be better off using RANK - see RANK (Transact-SQL) | Microsoft Docs[^]

Sql select group by error: field not contained in aggregate function or group by clause

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us