Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

670 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