Atrix, Check out the syntax for "delete duplicates"
After backing up your data... Delete duplicates from TableName using Budget, Account Rows where Budget, Account (and any other column you specify) are duplicated will be deleted, all other data will be ignored in the comparison. Things should get pretty simple. Like you said, you could "project NewTable from OldTable using Account, Budget" and have a useful table. Ben Petersen On 10 Oct 2002, at 22:31, Atrix Wolfe wrote: > ah yeah well this is a mal formed data file from a client, thast why > there are duplicates...they arent truely duplicates necesarily, its > just that the only columns which give us anything worthwile are > account and budget so i need to basicly make these unique somehow so > that i can do a project or some such straight into the database. I > was thinking it would be pretty straightforward to get unique rows > based on a column that was in duplicate (ie account) but i guess its > more complicated than i thought. > > ----- Original Message ----- > From: "Doug Daily" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, October 10, 2002 9:10 PM > Subject: Re: newbie sql question > > > > My first thought was: "Why are there duplicates?" > > > > If they are true duplicates, we can help you get rid or them. > > Then your question is easy to solve. > > > > If they are not true duplicates, then we need > > another field (column) to make the calculation unique. > > > > Douglas Daily > > Payroll Manager > > Facilities Services, box 352215 > > 206-221-4357 > > > > > > On Thu, 10 Oct 2002, Ben Petersen wrote: > > > > > Atrix, > > > > > > If you would like to remove the duplicates from the table you > > > could: > > > > > > Del dup from tableName > > > > > > Then just: > > > > > > Sel sum(Budget) from TableName > > > > > > Generally there wouldn't be a reason for exact duplicates... if > > > you don't want to remove the dups, maybe there is some other data > > > that would let you discriminate those records you want in a where > > > clause. > > > > > > Ben Petersen > > > > > > > > > > > > On 10 Oct 2002, at 12:57, Atrix Wolfe wrote: > > > > > > > This is a multi-part message in MIME format. > > > > > > > > ------=_NextPart_000_023E_01C2705C.A09840F0 > > > > Content-Type: text/plain; > > > > charset="iso-8859-1" > > > > Content-Transfer-Encoding: quoted-printable > > > > > > > > sorry, i didnt phrase my question correctly. If i have: > > > > > > > > Account Budget > > > > 1 30 > > > > 1 30 > > > > 2 40 > > > > 2 40 > > > > 3 20 > > > > 3 20 > > > > > > > > i want the sum of the distinct account budgets...IE: > > > > 30+40+20=3D90 so i = want it to display 90 as the result. Is > > > > this doable? > > > > > > > > ----- Original Message -----=20 > > > > From: randyp=20 > > > > To: [EMAIL PROTECTED]=20 > > > > Sent: Thursday, October 10, 2002 12:26 PM > > > > Subject: Re: newbie sql question > > > > > > > > > > > > SELECT account, SUM(budget) from [tableName] group by account. > > > > > > > > This will show you the sum of however many budget amounts for > > > > each = > > > > distinct account.=20 > > > > > > > > Account Budget > > > > 1 $ 200 > > > > 1 300 > > > > 2 100 > > > > > > > > will result in: > > > > 1 $ 500 > > > > 2 100 > > > > > > > > If you use SUM(budget)=3Ds the total of $ 600 will be listed = > > > > underneath the individual budget subtotals. > > > > > > > > Randy Peterson > > > > ----- Original Message -----=20 > > > > From: Atrix Wolfe=20 > > > > To: [EMAIL PROTECTED]=20 > > > > Sent: Thursday, October 10, 2002 2:39 PM > > > > Subject: newbie sql question > > > > > > > > > > > > i know this is more of an sql question (and a newbie one at > > > > that) = > > > > than an rbase question but im having a problem tryin to figure > > > > this out. > > > > > > > > I have a table with 2 columns...1 is Account and the other > > > > is = > > > > Budget. What i need is a sum of Budget, but there are duplicate > > > > entries = so i need it to sum Budget where Account is distinct. > > > > Anyone know how = to do this? > > > > > > > > thanx for the help! > > > > ------=_NextPart_000_023E_01C2705C.A09840F0 > > > > Content-Type: text/html; > > > > charset="iso-8859-1" > > > > Content-Transfer-Encoding: quoted-printable > > > > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > > > > <HTML><HEAD> <META http-equiv=3DContent-Type > > > > content=3D"text/html; = charset=3Diso-8859-1"> <META > > > > content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR> > > > > <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT > > > > face=3DArial size=3D2>sorry, i didnt phrase my question = > > > > correctly. =20 If i have:</FONT></DIV> <DIV><FONT > > > > face=3DArial size=3D2></FONT> </DIV> <DIV><FONT > > > > face=3DArial size=3D2>Account Budget</FONT></DIV> <DIV><FONT > > > > face=3DArial=20 > > > > size=3D2>1 > > > > &nb= sp; 30</FONT></DIV> <DIV><FONT > > > > face=3DArial=20 > > > > size=3D2>1 > > > > &nb= sp; 30</FONT></DIV> <DIV><FONT > > > > face=3DArial=20 > > > > size=3D2>2 > > > > &nb= sp; 40</FONT></DIV> <DIV><FONT > > > > face=3DArial=20 > > > > size=3D2>2 > > > > &nb= sp; =20 40</FONT></DIV> <DIV><FONT > > > > face=3DArial=20 > > > > size=3D2>3 > > > > &nb= sp; =20 20</FONT></DIV> <DIV><FONT > > > > face=3DArial=20 > > > > size=3D2>3 > > > > &nb= sp; =20 20</FONT></DIV> <DIV><FONT > > > > face=3DArial size=3D2></FONT> </DIV> <DIV><FONT > > > > face=3DArial size=3D2>i want the sum of the distinct account=20 > > > > budgets...IE: 30+40+20=3D90 so i want it to display 90 as the = > > > > result. Is=20 this doable?</FONT></DIV> <DIV><FONT > > > > face=3DArial size=3D2></FONT> </DIV> <BLOCKQUOTE > > > > dir=3Dltr=20 style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; > > > > MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; > > > > MARGIN-RIGHT: 0px"> > > > > <DIV style=3D"FONT: 10pt arial">----- Original Message ----- > > > > </DIV> <DIV=20 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; > > > > font-color: = > > > > black"><B>From:</B>=20 > > > > <A [EMAIL PROTECTED] = > > > > href=3D"mailto:[EMAIL PROTECTED]";>randyp</A> </DIV> > > > > <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A = > > > > [EMAIL PROTECTED]=20 > > > > href=3D"mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</A > > > > > </DIV> <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> > > > > Thursday, October 10, = > > > > 2002 12:26=20 > > > > PM</DIV> > > > > <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Re: newbie sql > > > > = > > > > question</DIV> > > > > <DIV><BR></DIV> > > > > <DIV><FONT face=3DArial>SELECT account, SUM(budget) from > > > > [tableName] = > > > > group by=20 > > > > account.</FONT></DIV> > > > > <DIV><FONT face=3DArial></FONT> </DIV> > > > > <DIV><FONT face=3DArial>This will show you the sum of however > > > > many = > > > > budget=20 > > > > amounts for each distinct account. </FONT></DIV> > > > > <DIV><FONT face=3DArial></FONT> </DIV> > > > > <DIV><FONT face=3DArial>Account > > > > Budget</FONT></DIV> <DIV><FONT > > > > face=3DArial>1 =20 > > > > $ 200</FONT></DIV> > > > > <DIV><FONT face=3DArial>1 > > > > =20 > > > > = > > > > 300</FONT></DIV> > > > > <DIV><FONT face=3DArial>2 > > > > =20 > > > > = > > > > 100</FONT></DIV> > > > > <DIV><FONT face=3DArial></FONT> </DIV> > > > > <DIV><FONT face=3DArial>will result in:</FONT></DIV> > > > > <DIV><FONT face=3DArial>1 > > > > =20 $ > > > > 500</FONT></DIV> <DIV><FONT face=3DArial>2 > > > > =20 > > > > = > > > > 100</FONT></DIV> > > > > <DIV><FONT face=3DArial></FONT> </DIV> > > > > <DIV><FONT face=3DArial>If you use SUM(budget)=3Ds the total > > > > of $ 600 = > > > > will be=20 > > > > listed underneath the individual budget > > > > subtotals.</FONT></DIV> <DIV><FONT > > > > face=3DArial></FONT> </DIV> <DIV><FONT > > > > face=3DArial> Randy=20 > > > > Peterson</FONT></DIV> <BLOCKQUOTE dir=3Dltr=20 > > > > style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: > > > > 5px; = > > > > BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> > > > > <DIV style=3D"FONT: 10pt arial">----- Original Message ----- > > > > </DIV> <DIV=20 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt > > > > arial; font-color: = > > > > black"><B>From:</B>=20 > > > > <A [EMAIL PROTECTED] > > > > href=3D"mailto:[EMAIL PROTECTED]";>Atrix = > > > > Wolfe</A> </DIV> > > > > <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A = > > > > [EMAIL PROTECTED]=20 > > > > href=3D"mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]< > > > > /A> = > > > > </DIV> > > > > <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Thursday, > > > > October 10, = > > > > 2002 2:39=20 > > > > PM</DIV> > > > > <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> newbie sql = > > > > question</DIV> > > > > <DIV><BR></DIV> > > > > <DIV><FONT face=3DArial size=3D2>i know this is more of an > > > > sql = > > > > question (and a=20 > > > > newbie one at that) than an rbase question but im having a > > > > problem = > > > > tryin to=20 > > > > figure this out.</FONT></DIV> > > > > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > > > > <DIV><FONT face=3DArial size=3D2>I have a table with 2 > > > > columns...1=20 is Account and the other is > > > > Budget. What i need is a sum = > > > > of=20 > > > > Budget, but there are duplicate entries so i need it to sum > > > > Budget = > > > > where=20 > > > > Account is distinct. Anyone know how to do > > > > this?</FONT></DIV> <DIV><FONT face=3DArial > > > > size=3D2></FONT> </DIV> <DIV><FONT face=3DArial > > > > size=3D2>thanx for the=20 > > > > help!</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML> > > > > > > > > ------=_NextPart_000_023E_01C2705C.A09840F0-- > > > > > > > > ================================================ > > > > TO SEE MESSAGE POSTING GUIDELINES: > > > > Send a plain text email to [EMAIL PROTECTED] > > > > In the message body, put just two words: INTRO rbase-l > > > > ================================================ > > > > TO UNSUBSCRIBE: send a plain text email to > > > > [EMAIL PROTECTED] In the message body, put just two words: > > > > UNSUBSCRIBE rbase-l > > > > ================================================ TO SEARCH > > > > ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > > > > > > > > > > ================================================ > > > TO SEE MESSAGE POSTING GUIDELINES: > > > Send a plain text email to [EMAIL PROTECTED] > > > In the message body, put just two words: INTRO rbase-l > > > ================================================ > > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > > ================================================ TO SEARCH > > > ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > ================================================ TO SEARCH ARCHIVES: > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In > the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
