How to get the total order amount without doubling

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to get the total order amount without doubling

Author

Topic

PoseyRobert
Starting Member

27 Posts

Posted - 2015-03-10 : 17:35:01

I am trying to get the order total without doubling the figures.

I keep getting the following error message

Error : Column 'ORDERS_SHIPMENTS.Order_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT os.Customer_ID as Trading_Partner_Nbr, c.Customer_Name as Trading_Partner_Name, '856' as Edi_Transaction_Code, count(os.Edi_856_Transaction_Date) as Edi_Transaction_Total, os.Edi_856_Transaction_Date as Edi_Transaction_Date

,(
select o.Order_Total_Amt
from ORDERS as o
where o.Order_ID = os.Order_ID ) as Total_Order_Amount

FROM ORDERS_SHIPMENTS as os
LEFT OUTER JOIN CUSTOMERS as c on os.Customer_ID = c.Customer_ID and c.Ship_To_Code = '-1'
where os.Edi_856_Transaction_Date >= '01/01/14' and os.Edi_856_Transaction_Date <= '12/31/14' and os.Customer_ID = 'B1425'
group by os.Customer_ID, c.Customer_Name, os.Edi_856_Transaction_Date
order by os.Customer_ID, c.Customer_Name

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-10 : 17:45:15

Perhaps this:


with CTE (Trading_Partner_Nbr, Trading_Partner_Name, Edi_Transaction_Code, Edi_Transaction_Total, Edi_Transaction_Date)
as
(
	SELECT 
		os.Customer_ID as Trading_Partner_Nbr, 
		c.Customer_Name as Trading_Partner_Name, 
		'856' as Edi_Transaction_Code, 
		count(os.Edi_856_Transaction_Date) as Edi_Transaction_Total
	FROM ORDERS_SHIPMENTS as os
	LEFT OUTER JOIN CUSTOMERS as c on os.Customer_ID = c.Customer_ID and c.Ship_To_Code = '-1'
	where os.Edi_856_Transaction_Date >= '01/01/14' and os.Edi_856_Transaction_Date <= '12/31/14' and os.Customer_ID = 'B1425'
	group by os.Customer_ID, c.Customer_Name, os.Edi_856_Transaction_Date
)
select 
	Trading_Partner_Nbr, 
	Trading_Partner_Name, 
	Edi_Transaction_Code, 
	Edi_Transaction_Total, 
	Edi_Transaction_Date, 
	o.Order_Total_Amt as Edi_Transaction_Total
from ORDERS as o
join CTE on o.Order_ID = cte.Order_ID

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

PoseyRobert
Starting Member

27 Posts

Posted - 2015-03-10 : 18:04:12

Thanks for the quick response.

Where do I get the Order_ID from. I do not want to display the order ID

select
Trading_Partner_Nbr,
Trading_Partner_Name,
Edi_Transaction_Code,
Edi_Transaction_Total,
Edi_Transaction_Date,
o.Order_Total_Amt as Edi_Transaction_Total
from ORDERS as o
join CTE on o.Order_ID = cte.Order_ID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-10 : 18:12:14

Oops sorry didn't notice Order_ID wasn't in the CTE. Add it to the CTE column list and GROUP BY. It won't be returned in the final output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

PoseyRobert
Starting Member

27 Posts

Posted - 2015-03-10 : 18:21:04

i am getting this error message

'CTE' has fewer columns than were specified in the column list.

;WITH
CTE (Trading_Partner_Nbr, Trading_Partner_Name, Edi_Transaction_Code, Edi_Transaction_Total, Edi_Transaction_Date, Order_ID)
AS
(
SELECT
os.Customer_ID as Trading_Partner_Nbr,
c.Customer_Name as Trading_Partner_Name,
'856' as Edi_Transaction_Code,
count(os.Edi_856_Transaction_Date) as Edi_Transaction_Total
FROM ORDERS_SHIPMENTS as os
LEFT OUTER JOIN CUSTOMERS as c on os.Customer_ID = c.Customer_ID and c.Ship_To_Code = '-1'
where os.Edi_856_Transaction_Date >= '01/01/14' and os.Edi_856_Transaction_Date <= '12/31/14' and os.Customer_ID = 'B1425'
group by os.Customer_ID, c.Customer_Name, os.Edi_856_Transaction_Date, os.Order_ID
)
select
Trading_Partner_Nbr,
Trading_Partner_Name,
Edi_Transaction_Code,
Edi_Transaction_Total,
Edi_Transaction_Date,
o.Order_Total_Amt as Edi_Transaction_Total
from ORDERS as o
join CTE on o.Order_ID = CTE.Order_ID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-10 : 18:27:44

Add Order_ID after count(os.Edi_856_Transaction_Date) in the CTE.

I am not sure if adding Order_ID in the CTE will affect your results since I don't understand the business rules here. If it does impact the results, then you'll need to add a join in the bottom portion to ORDER_SHIPMENTS and copy the same code.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

PoseyRobert
Starting Member

27 Posts

Posted - 2015-03-10 : 18:37:17

Oooh...

If I have a customer that I ship to 5 times.

I want to be able the get the total amount of the sales order from the header record.

customer b1425 has 5 orders on 01/01/14 and the total amount is 500.00.

So my transaction would be

CUSTOMER : B1425 DATE 01/01/14 Count 5 total amount 500.00

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-10 : 19:07:42

Does the ORDERS table have the individual amounts so you can do a SUM instead of using Order_Total_Amt?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

PoseyRobert
Starting Member

27 Posts

Posted - 2015-03-10 : 19:15:33

The ORDERS table contains the Total Amount of the order.

However the ORDERS_SHIPMENTS table contains the detail information.

I want to get 1 line of data with will show a total transactions for a give date and the total order amount for the transaction.

example

Customer B4125 Transaction date 01/01/14 Total Transactions 5 Total Order Amount 500.00

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-10 : 19:27:10

SUM ORDERS_SHIPMENTS then to make this easier. I assume it totals the same as ORDERS does.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

PoseyRobert
Starting Member

27 Posts

Posted - 2015-03-11 : 10:58:08

Thanks for you help Tara.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources