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/
