Group By When Using Aggregate Subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group By When Using Aggregate Subquery

Author

Topic

icanpee4hours
Starting Member

4 Posts

Posted - 2010-09-08 : 13:41:53

Hello there folks,

I just want to modify an existing query (with lots of joins), grouping the results by a specific column. I don't care about the occurance order(fisrt,last,min etc)

Using MIN() in all columns and GROUP BY (one col), does it gives me back the correct results for each returned column (remember, several joins)?

Secondly, how do I use GROUP BY (one col) if there is an aggregate subquery (nested select as a column)?

Thanks a lot in advance, b&gs!

Here Is The Modified Statement:

<<The original statement was the same except that I have added MIN() on all columns and a GROUP BY clause.
ECOM is the distinction column.
I think the query works fine, as long as I don't add the first column (RESERVED) which is a subquery>>


SELECT 
(SELECT ISNULL(SUM(ISNULL(Z1.QTY1,0)-Z1.QTY1COV-Z1.QTY1CANC)/ISNULL(A.MU21,0),0) FROM MTRLINES Z1, 
	RESTMODE Z2 WHERE Z1.MTRL = A.MTRL AND Z1.PENDING = 1 AND Z2.RESTCATEG = 2  AND Z1.COMPANY = Z2.COMPANY AND Z1.RESTMODE = Z2.RESTMODE) AS RESERVED,
MIN(UTB.NAME) AS DIMENSION, 
MIN(ROUND(ISNULL(C.QTY2, 0), 0)) AS WHOUSE_TODAY, 
MIN(UNITTYPE.NAME) AS UNITNAME, 
B.VARCHAR05 AS ECOM, 
MIN(A.NAME) AS NAME, 
MIN(A.PRICER) AS PRICE, 
MIN(A.PRICER01) AS WEBPRICE, 
MIN(A.REMARKS) AS REMARKS, 
MIN(A.SETITEM) AS SETITEM, 
MIN(COUNTRY.NAME) AS COUNTRYNAME, 
MIN(A.MTRL) AS PRD_ID, 
MIN(A.CODE), 
MIN(ISNULL(A.MU21, 0)) AS MU,  
MIN(A.MTRMODEL) AS MODEL 
FROM MTRL AS A 
LEFT OUTER JOIN  MTREXTRA AS B ON A.MTRL = B.MTRL 
LEFT OUTER JOIN MTRDATA AS C ON A.MTRL = C.MTRL 
LEFT OUTER JOIN UTBL01 AS UTB ON B.UTBL01 = UTB.UTBL01 
AND C.FISCPRD = 2010 
LEFT OUTER JOIN MTRUNIT AS UNITTYPE  ON A.MTRUNIT1=UNITTYPE.MTRUNIT 
AND UNITTYPE.COMPANY=1 
LEFT OUTER JOIN COUNTRY AS COUNTRY ON A.COUNTRY=COUNTRY.COUNTRY WHERE (A.COMPANY = 1) 
AND (A.SODTYPE = 51) 
AND (A.ISACTIVE = 1) 
AND (UTB.SODTYPE = 51) 
AND (UTB.COMPANY = 1) 
AND (NOT (B.VARCHAR05 IS NULL)) 
AND  (NOT (B.VARCHAR05 = '')) 
AND (A.MTRMODEL = 2045) 
GROUP BY B.VARCHAR05
ORDER BY ECOM, DIMENSION

CSears
Starting Member

39 Posts

Posted - 2010-09-08 : 14:18:49

Using Min in each column will return the lowest value in that column for the whole dataset.

As for where the group by clause should be placed, that depends on where you need to have the grouping applied in a nested query.

Example:

SELECT ISNULL(SUM(ISNULL(Z1.QTY1,0)-Z1.QTY1COV-Z1.QTY1CANC)/ISNULL(A.MU21,0),0) FROM MTRLINES Z1, 
	RESTMODE Z2 WHERE Z1.MTRL = A.MTRL AND Z1.PENDING = 1 AND Z2.RESTCATEG = 2  AND Z1.COMPANY = Z2.COMPANY AND Z1.RESTMODE = Z2.RESTMODE

The above is a query in and of itself. There is no need to place a group by statement in here since there are no constant terms, only a single aggregate. However you may want to reconsider having your denominator default to 0 if it's null. That can cause some nasty errors.

One thing to keep in mind when making your group by statement is that you must include all the non-aggregate terms. In the case of the query you posted, the RESERVED column is treated as a non-aggregate because it's value is a calculated constant, so you're group by statement may need to look like:

GROUP BY B.VARCHAR05, RESERVED

CSears
Starting Member

39 Posts

Posted - 2010-09-08 : 14:25:41

Guess I should clarify, the nested select statement contains a Aggregate Function, but since that select statement is evaluated inside the brackets and then used as if it were a value, it is not considered an aggregate by the outer select statement and therefore needs to be included in the group by clause. Just referencing the column name you assigned to it should suffice.

icanpee4hours
Starting Member

4 Posts

Posted - 2010-09-08 : 17:47:03

Thank you very much CSears, I really appreciate your time.
You have covered both of my questions.
The only problem is you cannot use a column alias in a group by clause.
Any workarounds??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources