a bit confusing

  • Thread starter Thread starter Andy Levy
  • Start date Start date
A

Andy Levy

Hi, apologies for the previous accidental post.

I currently have a table with the following values

ID Value GroupValue
1 10 50
1 10 50
1 10 50
1 10 50
1 10 50
2 6 18
2 6 18
2 6 18
3 87 87
4 2 10
4 6 10
4 2 10

As you can see each record has a Group ID number, and a value. Each record
also holds the total value of the group(GroupValue). I would like to design
an sql statement that will check if any records exist where the GroupValue
does not equal the total of all the Group's values.

I hope you get the gist.

I came up with the following statement - but it does not seem to work ....

SELECT ID, Value, GroupValue FROM myTable
WHERE (myTable.GroupValue <> (SELECT Sum(myTable.Value) AS SumValue FROM
myTable)
GROUP BY myTable.ID


Thanks

Andy
 
As you can see each record has a Group ID number, and a value. Each record
also holds the total value of the group(GroupValue). I would like to design
an sql statement that will check if any records exist where the GroupValue
does not equal the total of all the Group's values.
Click to expand...

This is exactly why most developers avoid storing a field like
GroupValue AT ALL. It's vulnerable to just this kind of error, and
there is no good way to ensure that you don't have invalid values,
since any change to any Value (or addition or deletion of a record)
will cause all those GroupValues to be just plain WRONG.

Typically one would store only the Value, and run a Totals query to
calculate the GroupValue on the fly as needed.
I hope you get the gist.

I came up with the following statement - but it does not seem to work ....

SELECT ID, Value, GroupValue FROM myTable
WHERE (myTable.GroupValue <> (SELECT Sum(myTable.Value) AS SumValue FROM
myTable)
GROUP BY myTable.ID
Click to expand...

If you must store this non-normalized, probably wrong value, and are
willing to put up with the considerable hassle of running this query
frequently and then figuring out which record is wrong, try including
the ID in the subquery to correlate the records:

SELECT ID, Value, GroupValue FROM myTable
WHERE (myTable.GroupValue <> (SELECT Sum(X.Value) AS SumValue FROM
myTable AS X WHERE X.ID = myTable.ID)
GROUP BY myTable.ID;
 
HI Thanks very muchfor your help.

I know it is not a clever thing to do - but its what i have been asked to
build, against my own advice.

Thanks again
 
I know it is not a clever thing to do - but its what i have been asked to
build, against my own advice.
Click to expand...

I'd be inclined to give the PHB who asked for this a Query and tell
them that it's at table. :-{(
 
Back
Top