SQL Query column sum problems

[Former Member]

Hi, can someone please help me, I am trying to add 6 rows of data columns and create a 7th column with the result of the sum.

prd.MCHB.CLABSprd.MCHB.CUMLMprd.MCHB.CINSMprd.MCHB.CEINMprd.MCHB.CSPEM+prd.MCHB.CRETM = SAP Qty

Am I able to do this with a SQL Query? If so, what syntax do I use and do I do it under a Grouping Expression?

Thanks

Mike

[Former Member · Accepted solution]

Hi,

I was successful in creating the mssql query which created 3 columns, with the third being a sum of 6 colmns. I did this under the "fixed query" mode and "fixed query" tab. Can I put this statement under the"Query" mode and "SQL Query details" tab?

select prd.MCHB.MATNR as Material_Number, prd.MCHB.CHARG as Lot_Number, prd.MCHB.CLABS + prd.MCHB.CUMLM + prd.MCHB.CINSM + prd.MCHB.CEINM + prd.MCHB.CSPEM + prd.MCHB.CRETM as SAP_Qty from prd.MCHB

[Former Member · Accepted solution]

You can. This whole string "prd.MCHB.CLABS + prd.MCHB.CUMLM + prd.MCHB.CINSM + prd.MCHB.CEINM + prd.MCHB.CSPEM + prd.MCHB.CRETM as SAP_Qty" would be entered in the ColumnName field on the SQL Query details tab. You would also have click the Add button to add the string to the list of columns.

[Former Member]

You can (and should) do this through a SQL query. You'll need to refer to vendor specific documentation for the SUM function, but in Microsoft SQL Server it is done like this:

select SUM(columnA + columnB + columnC) as total

from mytable

SUM is a group function so if you want any other columns in your select try this

select column1, column2, SUM(columnA + columnB + columnC) as total

from mytable

group by column1, column2