Thanks Rommel/Jim,
I thought it was like Order By and the number of columns specified was
optional.
Works fine. I may not even bother to sort by sum of extended prices.
Dennis
*****
At 09:54 AM 9/19/01 -0700, you wrote:
>Dennis,
>
>In this statement:
>
>edi t1.pr_nbr, t2.pr_des sum (t1.tr_unitp * t1.tr_qty) +
>from usage t1, parts t2 +
>whe t1.tr_code = "ox" +
>and t1.tr_date between "1/1/01" and "9/19/01" +
>and t1.pr_nbr = t2.pr_nbr +
>group by t1.pr_nbr +
>having sum (t1.tr_unitp * t1.tr_qty) > "5000"
>
>
>Try changing the group by clause from:
>
> group by t1.pr_nbr +
>
>to:
>
> group by t1.pr_nbr, t2.pr_des +
>
>
>Since you added another column that is not an aggregate function, you need
>to include that the appropriate group by clause.
>
>
>Rommel
>
> -----Original Message-----
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On
>Behalf Of Dennis Fleming
>Sent: Wednesday, September 19, 2001 9:18 AM
>To: [EMAIL PROTECTED]
>Subject: More Selection Wizard questions
>
>RBWin 6.5
>
>Was trying to give a customer a list of all parts used sorted by the sum of
>extended prices in decending order. (I may need a view.) 5000 of a specific
>part used at $1 would sort the same as one part at $5000. I gave them the
>following as a temp solution for all parts over $5000 for a date range.
>
>edi pr_nbr, sum (tr_unitp * tr_qty) +
>from usage +
>whe tr_code = "ox" +
>and tr_date between "1/1/01" and "9/19/01" +
>group by pr_nbr +
>having sum (tr_unitp * tr_qty) > "5000"
>
>BUT....
>
>In order to show the part descrption (after paying Albert the royalty fee
>on his patent) the following gets an "illegal column specification (2512)".
>
>edi t1.pr_nbr, t2.pr_des sum (t1.tr_unitp * t1.tr_qty) +
>from usage t1, parts t2 +
>whe t1.tr_code = "ox" +
>and t1.tr_date between "1/1/01" and "9/19/01" +
>and t1.pr_nbr = t2.pr_nbr +
>group by t1.pr_nbr +
>having sum (t1.tr_unitp * t1.tr_qty) > "5000"
>
>
>I've tweaked it all the ways I know how with no luck. So, I'd like to sort
>rows in decending order of sum of extended prices, and show a value from
>another table using select or edit all.
>
>TIA,
>
>Dennis
>****
>
>
>
>
>
>
>
>
>>YUP. My patented <grin> use of MAX to come up with a single value in table
>1
>>
>>SELECT t1.patnumbr,t1.rdate,t1.rtime,max(t1.totalcharge),sum(t2.charge)
>>FROM table1 t1, table2 t2
>>WHERE t1.patnumbr = t2.patnumbr
>> AND t1.rdate = t2.rdate
>> AND t1.rtime = t2.rtime
>>GROUP BY t1.patnumber,t1.rdate,t1.rtime
>>HAVING max(t1.totalcharge) <> sum(t2.charge)
>>
>>(You can use MIN, AVG or any other function that returns a single
>>value for totalcharge while excluding it from the grouping list.)
>>
>>
>>MJS <[EMAIL PROTECTED]> wrote:
>>
>>>I have 2 tables. The first has 4 columns, patnumbr, rdate rtime, and
>totalcharge
>>>(integer, date, time, and currency). It is linked to a second table in a
>1 to many
>>>relationship, where the columns are nearly the same....patnumbr, rdate,
>rtime, and
>>>charge. Each row in the first table is linked to a group of rows in the
>second
>>>table. The matching columns are the 1st 3 columns. As you might guess,
>the sum of
>>>the charges in the second table is the value of totalcharge in the first
>table. Now
>>>say something happens to corrupt the data, where the sum of the charges
>in the second
>>>table does not match the value in totalcharge, and I need to find out
>which row in the
>>>first table does not have the right value for totalcharge. Is there some
>really cool
>>>select statement, with group by and having or other forms of SQL magic
>that will find
>>>all off the rows in the first table that don't have the correct value in
>the
>>>totalcharge column???? It's easy to do with a declare cursor, but I was
>hoping that a
>>>select statement would work!
>>>
>>>TIA!
>>>
>>>Mike Sinclair
>>>
>>>
>>>
>>
>>
>>__________________________________________________________________
>>Your favorite stores, helpful shopping tools and great gift ideas.
>Experience the convenience of buying online with Shop@Netscape!
>http://shopnow.netscape.com/
>>
>>Get your own FREE, personal Netscape Mail account today at
>http://webmail.netscape.com/
>>
>>
>>
>Dennis Fleming
>IISCO
>http://www.TheBestCMMS.com
>
>
>
>
Dennis Fleming
IISCO
http://www.TheBestCMMS.com
