The sum in the query is too high

E

edu23more

Hello.-

I am having problems with a query that it might seem simple but is
giving me a lot of problems. Most of the values given by the query are
incorrect, they all seems to be a lot higher that what it should. The
SQL for this query is:

SELECT BAKINV.ITEM, BAKINV.ITEM_DESC, Sum(BAKINV.QTY_BY_LOC) AS
SumOfQTY_BY_LOC, Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) AS STOCK_VALUE,
Avg(([HISTEXT1.QTY_ISSUED]/6)) AS AvgMonthUsage, Sum(BAKINV.ON_ORD_QTY)
AS SumOfON_ORD_QTY, Sum([REQD_QTY]-[ISSUED_QTY]) AS FCST_QTY,
Avg(BAKINV.TOT_ROLCST) AS AvgOfTOT_ROLCST
FROM HISTEXT1 INNER JOIN (DEMANDS INNER JOIN BAKINV ON DEMANDS.ITEM =
BAKINV.ITEM) ON HISTEXT1.COMPONENT = DEMANDS.ITEM
WHERE (((BAKINV.ITEM_CLAS2)="3"))
GROUP BY BAKINV.ITEM, BAKINV.ITEM_DESC
HAVING (((BAKINV.ITEM) Like "v*"))
ORDER BY Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) DESC;


I am linking 3 different tables. Demands, Histext1 and Bakinv. I belive
I am doing this right but I know there is something wrong. The output
of this query should be something like:

ITEM,ITEM DESC,QTY_BY_LOC,STOCK_VALUE,
AVG_MONTH_USG,ON_ORD_QTY,FCST_QTY

Any other column from this query that is not mention before, is there
because if not, it doesn't event run the query.

Any help on this will be really appreciated.

Thanks in advance
 
G

Gary Walter

I cannot see your data but there will usually be
an aggregation difference if filter for ITEM LIKE "v*"
is in HAVING clause vs WHERE clause...

Did you really want to aggregate only those records
in a group whose


ITEM_Clas2 = "3" AND ITEM LIKE "v*"

As it stands, you are aggregating only those records
in a group whose

ITEM_CLAS2 = "3"

then (after the aggregation) throwing out groups where

ITEM LIKE "v*"

Do you see how that might affect aggregation values
(in particular, could cause them to be higher than
expected)?

As it stands, you
 
G

Gary Walter

make that:

then (after the aggregation) throwing out groups that
don't meet the condition

ITEM LIKE "v*"

"Gary Walter" crappily typed
I cannot see your data but there will usually be
an aggregation difference if filter for ITEM LIKE "v*"
is in HAVING clause vs WHERE clause...

Did you really want to aggregate only those records
in a group whose


ITEM_Clas2 = "3" AND ITEM LIKE "v*"

As it stands, you are aggregating only those records
in a group whose

ITEM_CLAS2 = "3"

then (after the aggregation) throwing out groups where

ITEM LIKE "v*"

Do you see how that might affect aggregation values
(in particular, could cause them to be higher than
expected)?

As it stands, you
I am having problems with a query that it might seem simple but is
giving me a lot of problems. Most of the values given by the query are
incorrect, they all seems to be a lot higher that what it should. The
SQL for this query is:

SELECT BAKINV.ITEM, BAKINV.ITEM_DESC, Sum(BAKINV.QTY_BY_LOC) AS
SumOfQTY_BY_LOC, Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) AS STOCK_VALUE,
Avg(([HISTEXT1.QTY_ISSUED]/6)) AS AvgMonthUsage, Sum(BAKINV.ON_ORD_QTY)
AS SumOfON_ORD_QTY, Sum([REQD_QTY]-[ISSUED_QTY]) AS FCST_QTY,
Avg(BAKINV.TOT_ROLCST) AS AvgOfTOT_ROLCST
FROM HISTEXT1 INNER JOIN (DEMANDS INNER JOIN BAKINV ON DEMANDS.ITEM =
BAKINV.ITEM) ON HISTEXT1.COMPONENT = DEMANDS.ITEM
WHERE (((BAKINV.ITEM_CLAS2)="3"))
GROUP BY BAKINV.ITEM, BAKINV.ITEM_DESC
HAVING (((BAKINV.ITEM) Like "v*"))
ORDER BY Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) DESC;


I am linking 3 different tables. Demands, Histext1 and Bakinv. I belive
I am doing this right but I know there is something wrong. The output
of this query should be something like:

ITEM,ITEM DESC,QTY_BY_LOC,STOCK_VALUE,
AVG_MONTH_USG,ON_ORD_QTY,FCST_QTY

Any other column from this query that is not mention before, is there
because if not, it doesn't event run the query.

Any help on this will be really appreciated.

Thanks in advance
Click to expand...
Click to expand...
 
E

edu23more

Thanks Gary for your quick response. In terms of the filters, yes, I
need to have them filter that way only Item_Class2=3 and all the Items
starting with V. I didn't understand your advise very well, but I
believe you are saying that I have to change the HAVING for a WHERE? if
I do this, I won't be able to show the column Item which I also need.

Could you please clarify a little bit better the aggregation problem
that you are refering to?

Thanks again.
 
G

Gary Walter

In terms of the filters, yes, I
need to have them filter that way only Item_Class2=3 and all the Items
starting with V. I didn't understand your advise very well, but I
believe you are saying that I have to change the HAVING for a WHERE? if
I do this, I won't be able to show the column Item which I also need.
Click to expand...

In the grid you probably had only one column for BAKINV

Field: ITEM
Table: BAKINV
Total: Group By
Sort:
Show: <checked>
Criteria: Like "v*"
or:

when your "Total:" row is "Group By,"
and you add a "Criteria," the filtering
will show up in the HAVING clause
(*after* all the grouping and aggregation
has taken place).

You need 2 columns in the grid for BAKINV

-- one for the Group By ("Total: Group By")
without any Criteria

-- one to add it to WHERE clause ("Total: Where")
with "Criteria: LIKE "v*"

{this may not print out clearly, but....2 columns...}

Field: ITEM ITEM
Table: BAKINV BAKINV
Total: Group By Where
Sort:
Show: <checked> <unchecked>
Criteria: Like "v*"
or:

This would move the filter up into the WHERE clause:

SELECT BAKINV.ITEM, BAKINV.ITEM_DESC, Sum(BAKINV.QTY_BY_LOC) AS
SumOfQTY_BY_LOC, Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) AS STOCK_VALUE,
Avg(([HISTEXT1.QTY_ISSUED]/6)) AS AvgMonthUsage, Sum(BAKINV.ON_ORD_QTY)
AS SumOfON_ORD_QTY, Sum([REQD_QTY]-[ISSUED_QTY]) AS FCST_QTY,
Avg(BAKINV.TOT_ROLCST) AS AvgOfTOT_ROLCST
FROM HISTEXT1 INNER JOIN (DEMANDS INNER JOIN BAKINV ON DEMANDS.ITEM =
BAKINV.ITEM) ON HISTEXT1.COMPONENT = DEMANDS.ITEM
WHERE
(
(BAKINV.ITEM_CLAS2 = "3" )
AND
(BAKINV.ITEM Like "v*")
)
GROUP BY BAKINV.ITEM, BAKINV.ITEM_DESC
ORDER BY Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) DESC;

This "says"

-- make groups based on distinct values of ITEM/ITEM_DESC

-- in each distinct group, only include the records where
ITEM_CLAS2 = "3"
AND
ITEM LIKE "v*"

-- with the filtered records in each distinct group,
then perform my summing/etc aggregations

============
The above may or may not be "why" (just best guess)....

your INNER JOIN's could also be "multiplying" records
within a group.

Maybe an easy test....

-- perform your SUM's on *only* the BAKINV table
in a query using WHERE's above and same grouping

Do you get same or different SUM's?

If different, then what you are *joining* to BAKINV
is probably the culprit.

Remember, your JOINS are creating records within
each distinct group. If one table is causing *dups* of records
within a group, your aggregates are going to be more than
you expect.

That's probably hard to understand.....

for a specific group in BAKINV
(say ITEM = "Varoom", ITEM_DESC = "Nissan")
say you have 4 records,
each with a QTY of 4.

So...over that group, SUM(QTY) = 4x4=16

But...if there are 2 records in DEMANDS
with that same ITEM = "Varoom"
then the JOIN will produce *8* records
within the group,
So... over that group, SUM(QTY) = 4x8=32!!!
 
G

Gary Walter

In terms of the filters, yes, I
need to have them filter that way only Item_Class2=3 and all the Items
starting with V. I didn't understand your advise very well, but I
believe you are saying that I have to change the HAVING for a WHERE? if
I do this, I won't be able to show the column Item which I also need.
Click to expand...

In the grid you probably had only one column for BAKINV.ITEM

Field: ITEM
Table: BAKINV
Total: Group By
Sort:
Show: <checked>
Criteria: Like "v*"
or:

when your "Total:" row is "Group By,"
and you add a "Criteria," the filtering
will show up in the HAVING clause
(*after* all the grouping and aggregation
has taken place).

You need 2 columns in the grid for BAKINV.ITEM

-- one for the Group By ("Total: Group By")
without any Criteria

-- one to add it to WHERE clause ("Total: Where")
with "Criteria: LIKE "v*"

{this may not print out clearly, but....2 columns...}

Field: ITEM ITEM
Table: BAKINV BAKINV
Total: Group By Where
Sort:
Show: <checked> <unchecked>
Criteria: Like "v*"
or:

This would move the filter up into the WHERE clause:

SELECT BAKINV.ITEM, BAKINV.ITEM_DESC, Sum(BAKINV.QTY_BY_LOC) AS
SumOfQTY_BY_LOC, Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) AS STOCK_VALUE,
Avg(([HISTEXT1.QTY_ISSUED]/6)) AS AvgMonthUsage, Sum(BAKINV.ON_ORD_QTY)
AS SumOfON_ORD_QTY, Sum([REQD_QTY]-[ISSUED_QTY]) AS FCST_QTY,
Avg(BAKINV.TOT_ROLCST) AS AvgOfTOT_ROLCST
FROM HISTEXT1 INNER JOIN (DEMANDS INNER JOIN BAKINV ON DEMANDS.ITEM =
BAKINV.ITEM) ON HISTEXT1.COMPONENT = DEMANDS.ITEM
WHERE
(
(BAKINV.ITEM_CLAS2 = "3" )
AND
(BAKINV.ITEM Like "v*")
)
GROUP BY BAKINV.ITEM, BAKINV.ITEM_DESC
ORDER BY Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) DESC;

This "says"

-- make groups based on distinct values of ITEM/ITEM_DESC

-- in each distinct group, only include the records where
ITEM_CLAS2 = "3"
AND
ITEM LIKE "v*"

-- with the filtered records in each distinct group,
then perform my summing/etc aggregations

============
The above may or may not be "why" (just best guess)....

your INNER JOIN's could also be "multiplying" records
within a group.

Maybe an easy test....

-- perform your SUM's on *only* the BAKINV table
in a query using WHERE's above and same grouping

Do you get same or different SUM's?

If different, then what you are *joining* to BAKINV
is probably the culprit.

Remember, your JOINS are creating records within
each distinct group. If one table is causing *dups* of records
within a group, your aggregates are going to be more than
you expect.

That's probably hard to understand.....

for a specific group in BAKINV
(say ITEM = "Varoom", ITEM_DESC = "Nissan")
say you have 4 records,
each with a QTY of 4.

So...over that group, SUM(QTY) = 4x4=16

But...if there are 2 records in DEMANDS
with that same ITEM = "Varoom"
then the JOIN will produce *8* records
within the group,
So... over that group, SUM(QTY) = 4x8=32!!!
 
E

edu23more

Hey Gary, Thanks again for all your support on this. I did the test
similar to what you suggested. I left the Item with the Group by with
the criteria together, but I deleted any other table on the
relationship and I just left BAKINV. So I got ITEM, ITEM DESC,
QTY_BY_LOC and ITEM_CLASS2.

The result for the Sum of QTY_BY_LOC is correct. Now if I added the
table DEMANDS to it and I do the relationship
BAKINV.ITEM->DEMANDS.ITEM, even without adding any other column, just
leaving the same fields mention above ITEM, ITEM DESC, QTY_BY_LOC and
ITEM_CLASS2 from table BAKINV, the result of Sum of QTY_BY_LOC is not
correct.

So I believe my problem is in the relationship, the JOINS. I know you
mention something about JOINS creating *dups* of records within a
group. Do you have any suggestions or posible solutions for this issue?

Thanks
 
G

Gary Walter

I did the test
similar to what you suggested. I left the Item with the Group by with
the criteria together, but I deleted any other table on the
relationship and I just left BAKINV. So I got ITEM, ITEM DESC,
QTY_BY_LOC and ITEM_CLASS2.

The result for the Sum of QTY_BY_LOC is correct. Now if I added the
table DEMANDS to it and I do the relationship
BAKINV.ITEM->DEMANDS.ITEM, even without adding any other column, just
leaving the same fields mention above ITEM, ITEM DESC, QTY_BY_LOC and
ITEM_CLASS2 from table BAKINV, the result of Sum of QTY_BY_LOC is not
correct.

So I believe my problem is in the relationship, the JOINS. I know you
mention something about JOINS creating *dups* of records within a
group. Do you have any suggestions or posible solutions for this issue?
Click to expand...

It is not immediately apparent to me what DEMANDS
brings to your Group By query (so following is guesswork)?

SELECT BAKINV.ITEM, BAKINV.ITEM_DESC, Sum(BAKINV.QTY_BY_LOC) AS
SumOfQTY_BY_LOC, Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) AS STOCK_VALUE,
Avg(([HISTEXT1.QTY_ISSUED]/6)) AS AvgMonthUsage, Sum(BAKINV.ON_ORD_QTY)
AS SumOfON_ORD_QTY, Sum([REQD_QTY]-[ISSUED_QTY]) AS FCST_QTY,
Avg(BAKINV.TOT_ROLCST) AS AvgOfTOT_ROLCST
FROM HISTEXT1 INNER JOIN (DEMANDS INNER JOIN BAKINV ON DEMANDS.ITEM =
BAKINV.ITEM) ON HISTEXT1.COMPONENT = DEMANDS.ITEM
WHERE
(
(BAKINV.ITEM_CLAS2 = "3" )
AND
(BAKINV.ITEM Like "v*")
)
GROUP BY BAKINV.ITEM, BAKINV.ITEM_DESC
ORDER BY Sum([BAKINV.TOT_ROLCST]*[QTY_BY_LOC]) DESC;

What fields in the SELECT clause come from DEMANDS?

It sounds like you want to create a preliminary query
(say "qryDemands") that groups on ITEM, then use
that query instead of the table in your original query.
Thus, qryDemands would only bring one record for
every distinct ITEM.

If you are aggregating on any field(s) in DEMANDS,
do it in the preliminary query.
 
G

Gary Walter

I did the test
similar to what you suggested. I left the Item with the Group by with
the criteria together, but I deleted any other table on the
relationship and I just left BAKINV. So I got ITEM, ITEM DESC,
QTY_BY_LOC and ITEM_CLASS2.

The result for the Sum of QTY_BY_LOC is correct. Now if I added the
table DEMANDS to it and I do the relationship
BAKINV.ITEM->DEMANDS.ITEM, even without adding any other column, just
leaving the same fields mention above ITEM, ITEM DESC, QTY_BY_LOC and
ITEM_CLASS2 from table BAKINV, the result of Sum of QTY_BY_LOC is not
correct.

So I believe my problem is in the relationship, the JOINS. I know you
mention something about JOINS creating *dups* of records within a
group. Do you have any suggestions or posible solutions for this issue?
Click to expand...

It sounds like you can identify data for at least one ITEM
where your sum is incorrect. Could you run 2 simple select queries
using the 2 tables and filtering for "thatoneitem," then provide
sample data from the 2 tables?

SELECT
B.ITEM,
B.QTY_BY_LOC,
B.TOT_ROLCST,
B.QTY_BY_LOC,
B..ON_ORD_QTYFROM
BAKINV As B
WHERE
(
(B.ITEM_CLAS2 = "3" )
AND
(B.ITEM = "thatoneitem")
);


SELECT
D.ITEM,
D.REQD_QTY,
D.ISSUED_QTY
FROM
DEMANDS As D
WHERE
D.ITEM = "thatoneitem";

I assumed

REQD_QTY
ISSUED_QTY

must be the fields coming from DEMANDS?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with query to sum top N values in group and rank the results 4

Top