| 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. |
|
|
|
|
How to get the total order amount without doubling