Sum() function wont work because of group by, what to do?

[Sl1ver]

i got sum() and count() functions in my sql query, now it doesnt work because i use group by statements, but if i dont groupby then i get errors telling me that i should group by what to do?

[ck9663]

Could you post here what you have so far?

~~ CK

[Sl1ver]

Look at the first select , you will see the sum function
and just a little down just after 'from' you will see the other calculation that form part of that.
This query return 11 results from the db
Take receivedqty for instance got all different qty's but when i do the sum function all i get duplicate of the data in a new column named TotalLineItems.
And the count function just returns 1 for each record, it should be 11

declare @PONum varchar(200);
set @PONum = 'RC-739942'

select 
convert(varchar(10), getdate(), 112) as [date],
Receives.poh__number as OrderNum, 
--Receives.poh__date as OrderDate, 
Receives.itm__code as Item,
isnull(Receives.itm__description, Receives.itm__variant)  as [description],
--Receives.pol__qty as OrderQty, 
Receives.poh__user9 as DeliveryNote,
Receives.scn__code as Scanner,
Receives.ith__date as ReceivedDate, 
Receives.iti__unload_count as ReceivedQty,
sum(Receives.[sum]) as TotalLineItems,
sum(Receives.[count]) as totallines,
--Checks.ith__name as CheckNum, 
--Checks.iti__qty as CheckQty, 
--Checks.ith__date as CheckDate, 
--Checks.iti__unload_count as CheckedQty,
--PutAways.ith__name as PutAwayNum, 
--PutAways.iti__qty as ToBePutAwayQty, 
--PutAways.ith__date as PutAwayDate, 
--PutAways.iti__unload_count as PutAwayQty,
Receives.usr__firstname as ReceiveUser
--Checks.usr__firstname as CheckUser, 
--PutAways.usr__firstname as PutAwayUser
from 
(select 
count(*) as [count], iti__unload_count as [sum], scn__code,poh__user9, convert(varchar,itm__description) as itm__description, convert(varchar,itm__variant) as itm__variant, ith__id, iti__id, poh__number, poh__user1, poh__date, itm__code, pol__qty, ith__name, ith__code, ith__date, iti__qty, iti__unload_count, usr__firstname
from bx_PurchaseOrderHeader R
join bx_PurchaseOrderLine RL on RL.pol__poh_id = R.poh__id
join bx_PurchaseOrderTransfer RT on RT.pot__poh_id = R.poh__id
join bx_ItemLocationTransferHeader RTH on RTH.ith__id = RT.pot__ith_id
join bx_ItemLocationTransferInstruction RTI on RTI.iti__ith_id = RTH.ith__id and RTI.iti__itm_id = RL.pol__itm_id
left join bx_itemlocationtransfertransaction on iti__id = ilt__iti_id
left join bx_session on ssn__id = ilt__ssn_id
left join bx_scanner on scn__id = ssn__scn_id
left join bx_user on usr__id = ssn__usr_id
join bx_Item I on I.itm__id = RL.pol__itm_id
where R.poh__number = @PONum /*R.poh__number like 'RC-%' and poh__date > getdate() - 5*/
and RTH.ith__ltt_id = 4
group by 
scn__code,
poh__user9,
ith__id, iti__id, poh__number,convert(varchar,itm__description),convert(varchar,itm__variant), poh__user1, poh__date, itm__code, pol__qty,ith__name,ith__code, ith__date, iti__qty, iti__unload_count, usr__firstname

) Receives
join 
(select 
ith__id, ith__name, ith__code, itm__code, iti__qty, iti__unload_count, ith__date, usr__firstname
from bx_ItemLocationTransferHeader CTH
join bx_ItemLocationTransferInstruction CTI on CTI.iti__ith_id = CTH.ith__id
left join bx_itemlocationtransfertransaction on iti__id = ilt__iti_id
left join bx_session on ssn__id = ilt__ssn_id
left join bx_scanner on scn__id = ssn__scn_id
left join bx_user on usr__id = ssn__usr_id
join bx_Item I on I.itm__id = iti__itm_id
where CTH.ith__ltt_id = 0
and CTH.ith__name like 'CK-%'
group by
ith__id, ith__name, ith__code, itm__code, iti__qty, iti__unload_count, ith__date, usr__firstname
) Checks on Checks.ith__id = Receives.ith__id + '_CHECK'
and Checks.itm__code = Receives.itm__code
left join 
(select 
ith__id, iti__id, poh__number, poh__user1, poh__user9, poh__date, itm__code, pol__qty, ith__name, ith__code, ith__date, iti__qty, iti__unload_count, usr__firstname
from bx_PurchaseOrderHeader P
join bx_PurchaseOrderLine PL on PL.pol__poh_id = P.poh__id
join bx_PurchaseOrderTransfer PT on PT.pot__poh_id = P.poh__id
join bx_ItemLocationTransferHeader PTH on PTH.ith__id = PT.pot__ith_id
join bx_ItemLocationTransferInstruction PTI on PTI.iti__ith_id = PTH.ith__id and PTI.iti__itm_id = PL.pol__itm_id
left join bx_itemlocationtransfertransaction on iti__id = ilt__iti_id
left join bx_session on ssn__id = ilt__ssn_id
left join bx_scanner on scn__id = ssn__scn_id
left join bx_user on usr__id = ssn__usr_id
join bx_Item I on I.itm__id = PL.pol__itm_id
where P.poh__number like 'PA-%' /*and poh__date > getdate() - 5*/
and PTH.ith__ltt_id = 5
group by
ith__id, iti__id, poh__number, poh__user1, poh__user9, poh__date, itm__code, pol__qty, ith__name, ith__code, ith__date, iti__qty, iti__unload_count, usr__firstname
) PutAways on PutAways.poh__user1 = Receives.poh__user1 and PutAways.itm__code = Receives.itm__code
and Checks.ith__name like '%'+PutAways.poh__user9+'%'
group by 
Receives.poh__number, 
Receives.poh__date, 
Receives.itm__code, 
Receives.pol__qty, 
Receives.ith__date, 
Receives.iti__unload_count,
Checks.ith__name, 
Checks.iti__qty, 
Checks.ith__date, 
Checks.iti__unload_count,
PutAways.ith__name, 
PutAways.iti__qty, 
PutAways.ith__date, 
PutAways.iti__unload_count,
Receives.usr__firstname, 
Checks.usr__firstname, 
PutAways.usr__firstname, 
Receives.itm__description, 
Receives.itm__variant,
Receives.[sum],
Receives.poh__user9,
Receives.scn__code,
Receives.[count]
order by Receives.poh__number, Receives.ith__date, PutAways.ith__date

[ck9663]

How about posting some sample data and how you want it returned, instead.

~~ CK

[Sl1ver]

got it sorted out, got the total count and sum of products calculated in the report writer. SHot for the help tho