October 16, 2008 at 6:57 am
Because customer 71586-01 has two orders my sum appears to sum by order_no
SELECT A.BUSINESS_UNIT
, A.LOAD_ID
, A.SHIP_TO_CUST_ID
, A.TMS_EXT_REF_ID
,A.ADDRESS_SEQ_NUM
,A.CUST_NAME
,C.CITY
,C.STATE
,C.POSTAL
,C.PHONE
,A.LINE_FIELD_C1_A
,A.LINE_FIELD_C10_B
, A.LINE_FIELD_C30_A
, SUM(A.QTY_SHIPPED) SHIPPING_QTY
,SUM(A.QTY_SHIPPED*B.SHIPPING_VOLUME) SHIPPING_VOLUME
FROM PS_IN_DEMAND A
,PS_INV_ITEM_UOM B
, PS_CUST_ADDRESS C
WHERE A.INV_ITEM_ID=B.INV_ITEM_ID
AND C.CUST_ID=A.SHIP_TO_CUST_ID
AND A.LOAD_ID <>' '
AND A.IN_FULFILL_STATE NOT IN ('10', '90')
GROUP BY A.BUSINESS_UNIT , A.ORDER_NO,A.LOAD_ID , A.SHIP_TO_CUST_ID , A.TMS_EXT_REF_ID ,
A.ADDRESS_SEQ_NUM ,A.CUST_NAME ,C.CITY ,C.STATE ,C.POSTAL ,C.PHONE ,A.LINE_FIELD_C1_A
,A.LINE_FIELD_C10_B , A.LINE_FIELD_C30_A
, A.LOAD_ID, A.SHIP_TO_CUST_ID
OUTPUT:
HM0010000005893000000106712167-01 40
HM001000000568300000010672545-01 50
HM0010000005918000000106771586-01 10
HM0010000005918000000106771586-01 10
HM0010000005917000000106772523-01 80
HM0010000005925000000106773941-01 60
HM0010000005922000000106775370-0170
October 16, 2008 at 7:31 am
You ARE summing it by order number... as well as all the other columns you have included in your group by clause.
GROUP BY A.BUSINESS_UNIT , A.ORDER_NO,A.LOAD_ID , A.SHIP_TO_CUST_ID , A.TMS_EXT_REF_ID ,
A.ADDRESS_SEQ_NUM ,A.CUST_NAME ,C.CITY ,C.STATE ,C.POSTAL ,C.PHONE ,A.LINE_FIELD_C1_A
,A.LINE_FIELD_C10_B , A.LINE_FIELD_C30_A
, A.LOAD_ID, A.SHIP_TO_CUST_ID
You aren't giving much information in your output list. It looks nothing like your select statement. The column names would be helpful for me to give you an exact answer, but presumably you want to see totals for those first four columns you are displaying. Remove all the other columns but those first four from your GROUP BY and run it again. If you really do want to see all of the customer information, then leave it in your GROUP BY, but take ORDER_NO out.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 16, 2008 at 8:15 am
I don't have order in the tsql that where I get lost
see attachments for the output
Thanks for you help
October 16, 2008 at 8:20 am
It may help to have a subquery in your FROM clause to do the summing:
...
FROM
(SELECT psid.SHIP_TO_CUST_ID,
SUM(psid.QTY_SHIPPED) SHIPPING_QTY,
SUM(psid.QTY_SHIPPED * psiiu.SHIPPING_VOLUME) SHIPPING_VOLUME
FROM PS_IN_DEMAND psid
INNER JOIN PS_INV_ITEM_UOM psiiu ON psid.INV_ITEM_ID = psiiu.INV_ITEM_ID
GROUP BY psid.SHIP_TO_CUST_ID) A
...
October 16, 2008 at 9:55 am
Please look at my first message again. You have ORDER_NO in your GROUP_BY clause. The group by clause controls the grouping for which sums will be calculated whether or not the grouping columns exist in the SELECT clause. Maybe this simple example will make it clearer.
--------------------------------------------------------------------------------------------
declare @sample table (AcctID varchar(10), orderNo int, amount numeric (10,2))
insert into @sample
select 'A12345',1,250
union all
select 'A12345',2,100
union all
select 'A12345',3,150
union all
select 'B99999',4,200
union all
select 'B99999',5,100
select AcctID,sum(amount) as ttlAmount
from @sample
group by acctID,orderNo--- orderNo is in the group by clause, but not the select clause
select AcctID,sum(amount) as ttlAmount
from @sample
group by acctID--- orderNo is not in the group by clause nor the select clause
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 16, 2008 at 10:07 am
I'm sorry I have removed the order_no from group by on my live Query and I thought that I had completely removed it from my test database but as you pointed out it was in the group by.
I am trying the sub query now
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply