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
