GROUP BY Non-ID Attribute

The GROUP BY Non-ID Attribute property controls whether or not non-ID attribute forms—like descriptions—are used in the GROUP BY. If you do not want non-ID columns in the GROUP BY, you can choose to use a MAX when the column is selected so that it is not used in the GROUP BY.

Levels at Which You Can Set This

Database instance, report, and template

Use Max (default)

Copy

select a11.MARKET_NBR MARKET_NBR,
 max(a14.MARKET_DESC) MARKET_DESC,
 a11.CLASS_NBR CLASS_NBR,
 max(a13.CLASS_DESC) CLASS_DESC,
 a12.YEAR_ID YEAR_ID,
 max(a15.YEAR_DESC) YEAR_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from MARKET_CLASS a11
 join LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
 join LOOKUP_CLASS a13
 on (a11.CLASS_NBR = a13.CLASS_NBR)
 join LOOKUP_MARKET a14
 on (a11.MARKET_NBR = a14.MARKET_NBR)
 join LOOKUP_YEAR a15
 on (a12.YEAR_ID = a15.YEAR_ID)
group by a11.MARKET_NBR, a11.CLASS_NBR,
a12.YEAR_ID

Use Group by

Copy

select a11.MARKET_NBR MARKET_NBR,
 a14.MARKET_DESC MARKET_DESC,
 a11.CLASS_NBR CLASS_NBR,
 a13.CLASS_DESC CLASS_DESC,
 a12.YEAR_ID YEAR_ID,
 a15.YEAR_DESC YEAR_DESC,
 sum(a11.TOT_SLS_DLR) TOTALSALES
from MARKET_CLASS a11
 join LOOKUP_DAY a12
 on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
 join LOOKUP_CLASS a13
 on (a11.CLASS_NBR = a13.CLASS_NBR)
 join LOOKUP_MARKET a14
 on (a11.MARKET_NBR = a14.MARKET_NBR)
 join LOOKUP_YEAR a15
 on (a12.YEAR_ID = a15.YEAR_ID)
group by a11.MARKET_NBR,
 a14.MARKET_DESC,
 a11.CLASS_NBR,
 a13.CLASS_DESC,
 a12.YEAR_ID,
 a15.YEAR_DESC