Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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

0
ruavol2
Asked:
ruavol2
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
Jim HornMicrosoft 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
 
PortletPaulCommented:
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
 
ruavol2Tableau 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
mlmccCommented:
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
 
ruavol2Tableau 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
 
ruavol2Tableau 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
 
PortletPaulCommented:
^^ ditto ^^   Cheers, Paul
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now