Solved

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

Posted on 2013-10-25
12
286 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 167 total points
Comment Utility
>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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points
Comment Utility
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
Comment Utility
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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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 100

Accepted Solution

by:
mlmcc earned 167 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:ruavol2
Comment Utility
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 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
Thank you guys very much.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the split.  Good luck with your project.  -Jim
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
^^ ditto ^^   Cheers, Paul
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Execution Plan 3 28
ms sql stored procedure 22 75
t-sql month question 8 40
Azure SQL DB? 3 13
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now