Ben, your solution is elegant and simple...thank you for sharing your knowledge and time (:
::salute:: ----- Original Message ----- From: "Ben Petersen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, October 10, 2002 2:57 PM Subject: Re: newbie sql question > 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:randyp@;ctags.com">randyp</A> </DIV> > > > > > <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A = > > > > > [EMAIL PROTECTED]=20 > > > > > href=3D"mailto:rbase-l@;sonetmail.com">[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:atrix2@;cox.net">Atrix = > > > > > Wolfe</A> </DIV> > > > > > <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A = > > > > > [EMAIL PROTECTED]=20 > > > > > href=3D"mailto:rbase-l@;sonetmail.com">[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/ ================================================ 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/
