Syntax for group by

[#1]

Good morning,

what is wrong with the syntax below? ForecastTemp1 does contain records,

Thanks in advance,

Leonardo

ForecastTemp1:
SQL select * FROM GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013 and Area = 'FCYEAR';

ForecastTemp2:
load
Area as ValueType,
CD as [Credit / Debit],
GISLE,

SenderKey as Order,
SenderKey & ' ' & Sender as [Order (Long)],
ReceiverKey as Account,
ReceiverKey & ' ' & Receiver as [Account (Long)],
if (Month > 9, '0' & Month, '00' & Month) as [Month (Long)],
Year,
Unit,
sum (TotalCost) as ForecastTotal,
sum (TotalQty) as ForecastTotalQty,
ServiceGroup
resident ForecastTemp1
group by Area, CD, GISLE, SenderKey, ReceiverKey, Year, Month, ServiceGroup;
drop tables ForecastTemp1;

[datanibbler]

Hi Leonardo,

in a LOAD with an aggregation function, the GROUP BY clause must contain ALL OTHER fields (except the ones where you have some aggregation fct.).

You might not want that - in that case you must not include the fields you do not want to group by in that LOAD, but join them to the aggregate table lateron.

HTH

[#3]

Hi Leonqrdo,

you need to add the non aggregated fields used in your load script to your group by, at the moment 'Sender', 'Receiver' and 'Unit' are missing from the group by

hope that helps

Joe

[fvelascog72]

Hi,

I think Unit, Sender and Receiver Fields may cause error syntax cause doesn´t have aggregation fucntion.

[buzzy996]

i think ur if statement causing the issue here,because of grouping.

[jagan · Accepted solution]

Hi,

You missed Sender, Receiver, Unit in the Group by clause.  Try below statement

ForecastTemp2:
load
Area as ValueType,
CD as [Credit / Debit],
GISLE,

SenderKey as Order,
SenderKey & ' ' & Sender as [Order (Long)],
ReceiverKey as Account,
ReceiverKey & ' ' & Receiver as [Account (Long)],
if (Month > 9, '0' & Month, '00' & Month) as [Month (Long)],
Year,
Unit,
sum (TotalCost) as ForecastTotal,
sum (TotalQty) as ForecastTotalQty,
ServiceGroup
resident ForecastTemp1
group by Area, CD, GISLE, SenderKey, ReceiverKey, Year, Month, ServiceGroup, Sender, Receiver, Unit;
drop tables ForecastTemp1;

[Peter_Cammaert]

Or if you're sure that Sender will be the same if SenderKey is the same and the same logic applies to ReceiverKey/Receiver (it usually does), use this (fewer GROUP BY fields):

ForecastTemp2:
load
Area as ValueType,
CD as [Credit / Debit],
GISLE,

SenderKey as Order,
SenderKey & ' ' & Only(Sender) as [Order (Long)],
ReceiverKey as Account,
ReceiverKey & ' ' & Only(Receiver) as [Account (Long)],
if (Month > 9, '0' & Month, '00' & Month) as [Month (Long)],
Year,
Unit,
sum (TotalCost) as ForecastTotal,
sum (TotalQty) as ForecastTotalQty,
ServiceGroup
resident ForecastTemp1
group by Area, CD, GISLE, SenderKey, ReceiverKey, Year, Month, ServiceGroup, Unit;
drop tables ForecastTemp1;

[sasiparupudi1]

try

ForecastTemp1:
SQL select * FROM GISdb.dbo."SAP_SO_Report" where CD = 'D' and Year > 2013 and Area = 'FCYEAR';

NoConcatenate

ForecastTemp2:
load
Area as ValueType,
CD as [Credit / Debit],
GISLE,

SenderKey as Order,
SenderKey & ' ' & Sender as [Order (Long)],
ReceiverKey as Account,
ReceiverKey & ' ' & Receiver as [Account (Long)],
if (Month > 9, '0' & Month, '00' & Month) as [Month (Long)],
Year,
Unit,
sum (TotalCost) as ForecastTotal,
sum (TotalQty) as ForecastTotalQty,
ServiceGroup
resident ForecastTemp1
group by Area, CD, GISLE, SenderKey, ReceiverKey, Year, Month, ServiceGroup;
drop tables ForecastTemp1;