Click here to Skip to main content
13,764,268 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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:

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 3 days ago
Updated 18hrs ago
v7
Comments
Bryian Tan 3 days ago
   
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 21hrs ago
   
I think I have the solution you need but the servers are a little busy at the moment. I will try again later
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
BikliWe Wiyao ALI 3 days ago
   
The provided link does not outline my question. Can you give me a sample code from my question to resolve the issue
OriginalGriff 3 days ago
   
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 ...
Rate this: bad
 
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
BikliWe Wiyao ALI 3 days ago
   
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 3 days ago
   
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?)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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:
;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
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[^]
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05-2016 | 2.8.181113.4 | Last Updated 13 Nov 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100