BAQ - Group by function

Kinetic ERP
1

Ive created a BAQ to show the Costs of parts in each delivery.

Select  
	[ShipHead].[Plant] as [ShipHead_Plant], 
	[ShipHead].[ShipDate] as [ShipHead_ShipDate], 
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum], 
	[ShipHead].[ShipStatus] as [ShipHead_ShipStatus], 
	[OrderHed].[DocOrderAmt] as [OrderHed_DocOrderAmt] 

from Erp.Part as [Part]
inner join Erp.ShipDtl as [ShipDtl] on 
	  Part.Company = ShipDtl.Company
	and  Part.PartNum = ShipDtl.PartNum
inner join Erp.ShipHead as [ShipHead] on 
	  ShipHead.Company = ShipDtl.Company
	and  ShipHead.PackNum = ShipDtl.PackNum
inner join Erp.OrderDtl as [OrderDtl] on 
	  ShipDtl.Company = OrderDtl.Company
	and  ShipDtl.OrderNum = OrderDtl.OrderNum
	and  ShipDtl.OrderLine = OrderDtl.OrderLine
inner join Erp.OrderHed as [OrderHed] on 
	  OrderDtl.Company = OrderHed.Company
	and  OrderDtl.OrderNum = OrderHed.OrderNum
group by 
	[ShipHead].[Plant], 
	[ShipHead].[ShipDate], 
	[OrderDtl].[OrderNum], 
	[ShipHead].[ShipStatus]

However I think I need to go a group by clause example in the above, which I have had a go at.

It comes up with a message Severity: Error, Text: Bad SQL statement. Review the server event logs for details. Correlation ID: ce132a39-68a8-4347-9b46-39fb66a2d698

I know I need to use the SUM function, but I’m not sure which ones to include and exclude.

Thank you in advance for any help and support.

2

You’d want to sum the [OrderHed].[DocOrderAmt] column. I.e., add a calculated field, whose value is:

sum(OrderHed.DocOrderAmt)

This calculated field will be used to replace the [OrderHed].[DocOrderAmt] column.
All the other output columns will need to have the GroupBy checkbox checked.

1 Like
3

Good Morning,

I’ve followed the advice, however I’m still having a problem. its still saying TEXT: BAD SQL… does anyone has every more ideas what I could do?

Select  
	[ShipHead].[Plant] as [ShipHead_Plant], 
	[ShipHead].[ShipDate] as [ShipHead_ShipDate], 
	[OrderDtl].[OrderNum] as [OrderDtl_OrderNum], 
	[ShipHead].[ShipStatus] as [ShipHead_ShipStatus], 
	(Sum(OrderHed_DocOrderAmt)) as [Calculated_TotalAmount] 

from Erp.Part as [Part]
inner join Erp.ShipDtl as [ShipDtl] on 
	  Part.Company = ShipDtl.Company
	and  Part.PartNum = ShipDtl.PartNum
inner join Erp.ShipHead as [ShipHead] on 
	  ShipHead.Company = ShipDtl.Company
	and  ShipHead.PackNum = ShipDtl.PackNum
inner join Erp.OrderDtl as [OrderDtl] on 
	  ShipDtl.Company = OrderDtl.Company
	and  ShipDtl.OrderNum = OrderDtl.OrderNum
	and  ShipDtl.OrderLine = OrderDtl.OrderLine
inner join Erp.OrderHed as [OrderHed] on 
	  OrderDtl.Company = OrderHed.Company
	and  OrderDtl.OrderNum = OrderHed.OrderNum
group by 
	[ShipHead].[Plant], 
	[ShipHead].[ShipDate], 
	[OrderDtl].[OrderNum], 
	[ShipHead].[ShipStatus]
4

You copied your value with an underscore “OrderHed_DocOrderAmt” in your SUM, change it to “OrderHed.DocOrderAmt”

3 Likes