Path: news.netfront.net!news.glorb.com!postnews.google.com!d21g2000prf.googlegroups.com!not-for-mail From: Ken Denny Newsgroups: comp.databases.oracle.misc Subject: Re: huge returned number problems Date: Tue, 15 Jan 2008 09:19:28 -0800 (PST) Organization: http://groups.google.com Lines: 52 Message-ID: <8eed4697-aef7-4bc4-baff-761b0f57180b@d21g2000prf.googlegroups.com> References: <9b42c15a-c7f3-480b-b833-a435d2cfe0bd@y5g2000hsf.googlegroups.com> NNTP-Posting-Host: 198.240.130.75 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1200417569 15642 127.0.0.1 (15 Jan 2008 17:19:29 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 15 Jan 2008 17:19:29 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: d21g2000prf.googlegroups.com; posting-host=198.240.130.75; posting-account=RcZQFAkAAABddx6ofaf6Hekal2qOY9qE User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; i-NavFourF; .NET CLR 1.1.4322; InfoPath.1),gzip(gfe),gzip(gfe) Xref: news.netfront.net comp.databases.oracle.misc:66908 On Jan 15, 10:17=A0am, "fitzjarr...@cox.net" wrote: > On Jan 15, 9:06=A0am, Totti wrote: > > > > > > > hi all, i want to Show total purchases by supplier in 2006. > > and i want to nclude only those suppliers from whom we made purchases > > of more than 2,000,000 during the first 6 months of 2006. > > so i did the following: > > > select purpmt.suppl_code as "Supplier", > > sum(purch.qty * purch.unit_price) "Total Purchases" > > from purch, purpmt , purinv > > having to_char(purinv.inv_date,'mm') < '07' > > and to_char(purinv.inv_date,'yy') =3D '06' > > and (purch.qty * purch.unit_price) > 2000000 > > and purinv.suppl_code =3D purpmt.suppl_code > > group by purpmt.suppl_code > > order by purpmt.suppl_code > > > but this is giving me huge number with +E12 and things imaginary, is > > there any wrong logic in my query or what would the problem be > > > help appreciated > > The HAVING clause is designed as a 'where' alternative for aggregate > values. =A0Possibly you should change this to read as follows: > > =A0select purpmt.suppl_code as "Supplier", > =A0sum(purch.qty * purch.unit_price) "Total Purchases" > =A0from purch, purpmt , purinv > =A0where to_char(purinv.inv_date,'mm') < '07' > =A0and to_char(purinv.inv_date,'yy') =3D '06' > =A0and (purch.qty * purch.unit_price) > 2000000 > =A0and purinv.suppl_code =3D purpmt.suppl_code > =A0group by purpmt.suppl_code > =A0order by purpmt.suppl_code; > > I expect you'll see different, and more accurate, results. > > David Fitzjarrell and (purch.qty * purch.unit_price) > 2000000 would limit it to individual purchases of more than 2 million. That part needs to move from the where clause to the having clause: having sum(purch.qty * purch.unit_price) > 2000000