GROUP BY question

1. Senior Member
Join Date
Dec 1969
Posts
1,686

## GROUP BY question

Please consider the following:<BR><BR>SELECT DISTINCT CDB_CustomerNum, CDB_CheckNum,<BR>SUM(convert(decimal(15,4), Amount)) FROM CHKS<BR>GROUP BY CDB_CustomerNum, CDB_CheckNum <BR>WITH ROLLUP<BR><BR>This is pulling up a grouped recordset of customernumbers with checks, like so:<BR><BR>CustNum &#124 Chk No &#124 Amt<BR>ABC001 1 35.00<BR>ABC001 2 45.00<BR>ABC001 3 65.00<BR>ABC001 NULL 145.00<BR>etc etc<BR><BR>which is OK. There is another field, however, that is unique AND can be applied multiple times to a single record. Its called the Errors field, and a single record w/o a GROUP BY will look like this:<BR><BR>ABC001 1 35.00 Dealer Number is invalid.<BR>ABC001 1 35.00 Check for improper amount<BR>ABC001 1 35.00 Bad invoice number<BR>etc etc. <BR><BR>The relationships are 1 Customer number to many checks to many errors.<BR><BR>I need to include the errors with the GROUP BY specified above. What I&#039;m looking for is:<BR><BR>ABC001 1 35.00 Dealer Number is invalid.<BR>ABC001 1 35.00 Check for improper amount<BR>ABC001 1 35.00 Bad invoice number<BR>ABC001 2 45.00 <BR>ABC001 3 65.00<BR>ABC001 NULL 145.00<BR><BR>Where the SUM calculated is only calculated once per check number.<BR><BR>I&#039;m still not a SQL guru. Where do I look?<BR><BR>Thanks,<BR>BB<BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Looks to me...

...like you just need to JOIN the *results* of your GROUP BY with the Errors table?<BR><BR>What DB is this?<BR><BR>If SQL Server, you could create a VIEW that would have the GROUP BY results and then just do a query that joined that view and the errors table.<BR><BR>*PROBABLY* you can do this without even needing the VIEW.<BR><BR>In Access, I dunno if you can do this without creating a temp table?<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
1,686

## SQL Server 7

.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

I think you should *REALLY* consider moving that Errors field out to a separate table!<BR><BR>Ummm...but...<BR><BR>How come you would have the same check number in the table twice? (If it weren&#039;t for the Errors field, that is.)<BR><BR>Because a check might have various amounts applied to different accounts?<BR><BR>I don&#039;t get this. <BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
1,686

## ok, better explanation

Heres the hierarchy<BR><BR>1 Account to many checks<BR>1 check to many errors<BR><BR>A check number is stored in the table twice (or 3 times, etc) to show that there are multiple errors associated with a single check. <BR><BR>So Customer ABC100&#039;s check 101 can have 3 things wrong with it. check 102 can have 2 things, and check 103 can have none, etc etc.<BR><BR>I want to aggregate the checks individually and not add the check amount more than once, even though there may be more than 1 thing wrong with the check.<BR><BR><BR>I didnt write the DB schema, and I cant consider changing it right now.

6. Senior Member
Join Date
Dec 1969
Posts
1,686

## ps Bill

Im taking off for home so that i can VPN to work (sigh). If you have more Q&#039;s ill be back in half hr or so.<BR><BR>Thanks Bill ;-)

7. Senior Member
Join Date
Dec 1969
Posts
1,686

## pps

Bear in mind that I still want to list the errors individually...

8. Senior Member
Join Date
Dec 1969
Posts
96,118

&#062; A check number is stored in the table twice (or 3 times, etc)<BR>&#062; to show that there are multiple errors associated with a single check.<BR><BR>Naughty naughty naughty!<BR><BR>Time for ANOTHER TABLE!<BR><BR>Table name: CheckErrors<BR> custID: link to both Accounts table and Checks table<BR> checkNum: link to Checks table<BR> errorCode: number<BR> errorMsg: text (though maybe this should be yet another table?)<BR><BR>Now get rid of that Errors field from your Checks table.<BR><BR>All of a sudden this is a piece of cake!<BR><BR>Now you don&#039;t need to do SUM(...) to get the amount of the check! (Shouldn&#039;t have been doing SUM in any case. Should have been using AVG or MIN or MAX, since you wanted a *SINGLE* value, yes?)<BR><BR>Oh, heck... Using your existing table (yuck) why couldn&#039;t you just do:<BR><BR>SELECT Acct, CheckNum, Amount, ErrorMsg <BR>FROM checks<BR>ORDER BY Acct, CheckNum, ErrorMsg<BR><BR>???<BR><BR>I *really* don&#039;t understand the point of the SUM in all that, no matter what you are doing.<BR><BR><BR>

9. Senior Member
Join Date
Dec 1969
Posts
1,686

## Bill -

Youre missing the point ;-) Its not right; but it is what it is. I didnt put it together and I&#039;m not at liberty to change the schema.<BR><BR>Since an account can have multiple checks (which in turn can have multiple errors), I need to aggregate the ENTIRE dollar amount assigned to each account, while also aggregating the errors to a single check. My users require that they see the error type(s) associated with each check, while still totalling the sum of all the checks. Thats why I need the sum.<BR><BR>I cant remove the Errors field. Several stored procedures depend on it and I dont have time to hunt them down to fix them.<BR><BR>Does that make any sense? <BR><BR>Rephrase:<BR>Do you see the situation now?<BR>

10. Senior Member
Join Date
Dec 1969
Posts
1,686

## ppps

as you can see im still here. waiting on the answer (if any) for this one... its quite a quandry

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•