Combining two databases I need to multiply two values together and hand it to the TEMP table
I have two fields Delivery_Quantity and Base Price. I need to multiply those together and then pass it to a TEMP table. The following code is just one side of the database I need to merge.
I was trying to combine the two Delivery_Quantity * Base_Price will equal Gross Sales.
I have tried
I get the following error every time I try to use it. I just do not understand why all the aggregate function errors in the statements when I put it in.
I am missing something major in SQL I think I need an example of why you need one...?
If I leave in the GROSSAMT out i get the following errors.
I was trying to combine the two Delivery_Quantity * Base_Price will equal Gross Sales.
I have tried
SELECT
ROSS_SALESHDR.OH_DELIVERY_DATE,
ROSS_SALESDTL.OD_RECORD_TYPE,
ROSS_SALESDTL.OD_PRODUCT,
--GROSSAMT = SUM(ROSS_SALESDTL.OD_TOTAL_DELIV_QUANTITY * OD_BASE_PRICE),
ROSS_SALESDTL.OD_TOTAL_DELIV_EXTENSION,
ROSS_CUSTOMER.CM_CHAINID,
ROSS_CUSTOMER.CM_CUSTKEY,
ROSS_CUSTOMER.CM_FULLNAME,
ROSS_CUSTOMER.CM_DELIVZONE,
ROSS_PRODUCTS.PRD_P_UNITCASE,
RAMS_CrossRef.RAMS_CHAIN_NO,
RAMS_CrossRef.SNAK_CHAIN_NO,
RAMS_CrossRef.CHAIN_IDENT
FROM
CHLROS05.RAMSDB.dbo.ROSS_SALESHDR ROSS_SALESHDR
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER ROSS_CUSTOMER
ON ROSS_SALESHDR.OH_CUSTOMER_NUMBER = ROSS_CUSTOMER.CM_CUSTKEY
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_SALESDTL ROSS_SALESDTL
ON ROSS_SALESHDR.OH_TICKET_NUMBER = ROSS_SALESDTL.OD_TICKET_NUMBER
AND ROSS_SALESDTL.OD_RECORD_TYPE = 'S' --,'R','M'
INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS ROSS_PRODUCTS
ON ROSS_SALESDTL.OD_PRODUCT = ROSS_PRODUCTS.PRD_PRODUCT
LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE1
ON ROSS_CUSTOMER.CM_SELLZONE = ROSS_IDCODE1.ID_FIELD
LEFT OUTER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE ROSS_IDCODE2
ON ROSS_PRODUCTS.PRD_A_GLTYPE = ROSS_IDCODE2.ID_FIELD
INNER JOIN WebSnakDS_SNYB_test.dbo.RAMS_CrossRef RAMS_CrossRef
ON ROSS_CUSTOMER.CM_CHAINID = RAMS_CrossRef.RAMS_CHAIN_NO
/*WHERE
ROSS_SALESHDR.OH_DELIVERY_DATE >= {ts '2013-01-01 00:00:00.00'} AND
ROSS_SALESHDR.OH_DELIVERY_DATE < {ts '2013-04-30 00:00:00.00'} AND
ROSS_SALESDTL.OD_PRODUCT >= '0*' AND
ROSS_SALESDTL.OD_PRODUCT <= '9999*' AND
ROSS_SALESDTL.OD_RECORD_TYPE = 'O' AND
(ROSS_CUSTOMER.CM_DELIVZONE = '400002' OR
ROSS_CUSTOMER.CM_DELIVZONE = '400001')
*/I get the following error every time I try to use it. I just do not understand why all the aggregate function errors in the statements when I put it in.
I am missing something major in SQL I think I need an example of why you need one...?
If I leave in the GROSSAMT out i get the following errors.
Msg 8118, Level 16, State 1, Line 1
Column 'ROSS_SALESHDR.OH_DELIVERY_DATE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'ROSS_SALESDTL.OD_RECORD_TYPE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'ROSS_SALESDTL.OD_PRODUCT' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'ROSS_SALESDTL.OD_TOTAL_DELIV_EXTENSION' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'ROSS_CUSTOMER.CM_CHAINID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'ROSS_CUSTOMER.CM_CUSTKEY' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'ROSS_CUSTOMER.CM_FULLNAME' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'ROSS_CUSTOMER.CM_DELIVZONE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'ROSS_PRODUCTS.PRD_P_UNITCASE' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'RAMS_CrossRef.RAMS_CHAIN_NO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'RAMS_CrossRef.SNAK_CHAIN_NO' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Msg 8118, Level 16, State 1, Line 1
Column 'RAMS_CrossRef.CHAIN_IDENT' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Correct. Ev - er - y column.
The only real way to avoid this, if you don't want to type 50 or so column names, is to do your aggregating in a subquery, then call that value, e.g.
The only real way to avoid this, if you don't want to type 50 or so column names, is to do your aggregating in a subquery, then call that value, e.g.
SELECT f.id, f.name, f_sum.some_number_sum
FROM fruit f
JOIN (SELECT id, SUM(some_number) as some_number_sum
FROM fruit
GROUP BY ID) f_sum ON f.id = f_sum.idASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That fixed it ...okay now I am curious as to the why did I not have to group on the GrossAmt field...? is that because technically it is now an aggregate function and therefore does not need to be grouped by...?
GrossAmt is the result of the calculation.
When you do aggregation, every field must be an aggregate or in a group by
mlmcc
When you do aggregation, every field must be an aggregate or in a group by
mlmcc
ASKER
Thank you guys very much.
Thanks for the split. Good luck with your project. -Jim
^^ ditto ^^ Cheers, Paul
ASKER
I will be honest that seems strange to have to do that way........but okay. I am just confused that it is that way. If I understood the why I will remember it.