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
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')
    */

Open in new window


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.

Open in new window

Fletcher BurdineTableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mlmccConnect With a Mentor Commented:
The group by clause is required so SQL knows how to perform the aggregation.

The group by order does matter.  You can't just list fields.  You have to consider what sums you want.

For instance If you have CustomerId, City, State and Sales being pulled

SELECT CustomerID, City, State, Sum(SalesAmount) as TotalSales
From You SalesTable

Do you want that Total by Customer, then City, then state
This would give you the total for the customer then break it down by city and then by state

If you did State, city customer
You would get total by state broken down by city then by customer

Just as changing the grouping in a report changes the report so does changing the grouping of the aggregation.

mlmcc
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>I get the following error every time I try to use it.
If any column in the SELECT clauses uses an aggregate such as SUM or COUNT, then there needs to be a GROUP BY clause with all non-aggregated columns, and your T-SQL statement does not have a GROUP BY clause.

Here's an article that lays it out for you: SQL Server Group By Solutions.  Scroll down to 'Notice that in the above queries all columns in the SELECT clause that are NOT '
0
 
PaulConnect With a Mentor Commented:
to utilize the SUM() function in that query, you need a group by clause like this
GROUP BY
        ROSS_SALESHDR.OH_DELIVERY_DATE,
        ROSS_SALESDTL.OD_RECORD_TYPE,
        ROSS_SALESDTL.OD_PRODUCT,
        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

Open in new window

{+ edit} this goes after the where clause :)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
So let me make sure I am clear. You are saying if at anytime I create a SUM or Count etc in the Select Statement then "all non-aggregated columns' meaning every value in the list not just the one that I summed or counted on needs to get the Group BY set to it.

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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
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.id

Open in new window

0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
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...?
0
 
mlmccCommented:
GrossAmt is the result of the calculation.

When you do aggregation, every field must be an aggregate or in a group by

mlmcc
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Thank you guys very much.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
 
PaulCommented:
^^ ditto ^^   Cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.