Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Combining two databases I need to multiply two values together and hand it to the TEMP table

Posted on 2013-10-25
12
Medium Priority
?
302 Views
Last Modified: 2013-10-28
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
Comment
Question by:ruavol2
  • 3
  • 3
  • 2
  • +1
12 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 668 total points
ID: 39600485
>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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 664 total points
ID: 39600491
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
 

Author Comment

by:ruavol2
ID: 39600562
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 39600578
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 668 total points
ID: 39601119
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
 

Author Comment

by:ruavol2
ID: 39601188
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 39601213
GrossAmt is the result of the calculation.

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

mlmcc
0
 

Author Closing Comment

by:ruavol2
ID: 39606116
Thank you guys very much.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39606203
Thanks for the split.  Good luck with your project.  -Jim
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39607336
^^ ditto ^^   Cheers, Paul
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question