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?
4 1792
Could you post here what you have so far?
~~ CK
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
-
How about posting some sample data and how you want it returned, instead.
~~ CK
got it sorted out, got the total count and sum of products calculated in the report writer. SHot for the help tho
Sign in to post your reply or Sign up for a free account.
Similar topics
by: MX1 |
last post by:
I have a query written in MS Access that has a few calculated fields. Is it
possible to refer to that query in a form field. I'd like the form field to
show the sum of one of the columns from the...
|
by: Kevin Carter |
last post by:
Hi
i have a table that contains several fields
field 1(area), field2(quantity),Field3(price)
What i want to do is
return total quatity and total price against a distict area
i have the...
|
by: peter pilsl |
last post by:
I have a question to sql and dont know exactely if this is the right
place to ask. If not I would be glad if anyone could point me to the
correct group/list/resource. Thnx.
I want to perform a...
|
by: dba123 |
last post by:
THERE HAS TO BE A SOLUTION FOR THIS!!!! How can I get around the limitation in SSRS 2005 of being able to SUM a Group referenced field in my FOOTER!!!
It's driving me nuts
My footer field's...
|
by: David |
last post by:
Hi,
I have an asp page which prints data as follows:
The sum in brackets is a calculated result on the page, SQ *
RS2("SelectorQty"), a variable SQ which holds a qty multiplied by a
record...
|
by: Rudolf Bargholz |
last post by:
Perhaps some kind soul could help me out with an SQL I have been trying all
day to get to work, where one colum is just not summing up the way I want it
to.
I have the following data
GRP_SEQ ...
|
by: gillian3114 |
last post by:
why this sum function won't work ?
eg Sum(personal)
lblPersonal.Caption it wont retrieve the sum value of personal
Dim cn6 As New ADODB.Connection
Dim strCNString4 As String
Dim rs6 As New...
|
by: gillian3114 |
last post by:
why this sum function won't work ?
eg Sum(personal)
lblPersonal.Caption it wont retrieve the sum value of the personal field
Dim cn6 As New ADODB.Connection
Dim strCNString4 As String
Dim rs6...
|
by: dwasbig9 |
last post by:
Hi Group (fairly limited knowledge of Access and almost none of Access
VBA. Using Access 2003).
I need to sum time, I've found through the groups archive an sql
extract that led me to this
...
|
by: bearophileHUGS |
last post by:
Empty Python lists don't know the type of the items it will
contain, so this sounds strange:
0
Because that may be an empty sequence of someobject:
0
In a statically typed language in...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| | |