Group by clause

[dmxmikey]

I have the below and when i load im getting an error aggregation expressions required by GROUP clause;

Load
DATE([PURCHASE_HEADERS.del-date],'YYYY-MM-DD') as Date ,
[supp-code] as Supplier,
if([supp-code]='','Dummy',[supp-code]) & '-' & [p-code] as Product,
'All Customers' as [Sold To],
'Purchases' as [Transaction Type],
[PURCHASE_DETAILS.act-qty] as Units,
NUM(Round((([PURCHASE_DETAILS.act-cost] * [PURCHASE_DETAILS.act-qty])),0.01),'#,##0.00;-#,##0.00') as Value,
'GBP' as Currency,
'' as [External Reference],
'' as [Interface Date],
'' as [Agreement ID],
'' as [Advised Earnings],
[po-no] as [Order Reference],
'' as [Delivery Reference],
'' as [Invoice Reference]
Resident SUPPLIER
where not IsNull([p-code]) and DATE([PURCHASE_HEADERS.del-date]) >='27/12/2021' and DATE([PURCHASE_HEADERS.del-date]) <='04/06/2022'
GROUP By [PURCHASE_HEADERS.del-date],[supp-code],[p-code],[po-no]
;

[marcus_sommer]

You need to apply all fields which aren't aggregated within the group by.

- Marcus

[dmxmikey]

Thanks Marcus, have done that as below but still get an error?

NoConcatenate
Final_Export:

Load
DATE([PURCHASE_HEADERS.del-date],'YYYY-MM-DD') as Date ,
[supp-code] as Supplier,
if([supp-code]='','Dummy',[supp-code]) & '-' & [p-code] as Product,
'All Customers' as [Sold To],
'Purchases' as [Transaction Type],
[PURCHASE_DETAILS.act-qty] as Units,
NUM(Round((([PURCHASE_DETAILS.act-cost] * [PURCHASE_DETAILS.act-qty])),0.01),'#,##0.00;-#,##0.00') as Value,
'GBP' as Currency,
'' as [External Reference],
'' as [Interface Date],
'' as [Agreement ID],
'' as [Advised Earnings],
[po-no] as [Order Reference],
'' as [Delivery Reference],
'' as [Invoice Reference]
Resident SUPPLIER
where not IsNull([p-code]) and DATE([PURCHASE_HEADERS.del-date]) >='27/12/2021' and DATE([PURCHASE_HEADERS.del-date]) <='04/06/2022'
GROUP By [PURCHASE_HEADERS.del-date],[supp-code],[p-code],[po-no],[PURCHASE_DETAILS.act-cost],[PURCHASE_DETAILS.act-qty]
;

[marcus_sommer · Accepted solution]

I just see - there is no aggregation like a sum() or count() included and any is mandatory to apply a group by.

- Marcus

[GnaneswarReddy]

As Sommer Said , group by comes handy when we are using Aggregation Functions . 
In your case , I do not see any Aggregation functions used in your script. How ever if you wish to use Group by , only option is to use all fields in you group by clause and do not use Field/Column Aliases as Group by will not recognize and will throw Error.