Dennis:
You added a column to the select. You gotta add it to the group by also. Plus, add a
comma after the second column in the select.
Jim Blackburn
Kodiak
Dennis Fleming wrote:
>
> 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
