| need help fast [message #373222] |
Wed, 04 April 2001 16:24  |
Richard streets
Messages: 1
Registered: April 2001
|
Junior Member |
|
|
QL> l
1 select name as "NAME", area as "AREA", phone as "PHONE",
2 ename as "SALES REP", sum(total) as "TOTAL PURCHASES"
3 from demo.emp, demo.ord, demo.customer
4 where customer.custid = ord.custid
5 and customer.repid = emp.empno
6 group by name, area, phone, ename
7 having sum(total)=(select max(sum(total)) from demo.ord) or
8* sum(total)=(select min(sum(total)) from demo.ord) group by total
i want to have only the company with the max purchases and min purchases returned from the query. any help would be appreciated.
richard
|
|
|
|
| Re: need help fast [message #373226 is a reply to message #373222] |
Thu, 05 April 2001 05:31  |
padma
Messages: 22
Registered: April 2001
|
Junior Member |
|
|
|
select "NAME","AREA","PHONE",ename "SALES REP",sum(total) "TOTAL PURCHASES" from demo.emp,demo.ord,demo.customer where customer.custid=order.custid and customer.repid=emp.empno group by name,area,phone,ename having sum(total)=select max(sum(total)) from demo.ord or sum(total)=select min(sum(total)) from demo.ord group by total.
|
|
|
|
| Re: need help fast [message #373228 is a reply to message #373222] |
Thu, 05 April 2001 08:33 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
select name as "NAME", area as "AREA", phone as "PHONE", ename as "SALES REP", max(sum(total)) as "TOTAL PURCHASES"
from demo.emp, demo.ord, demo.customer
where customer.custid = ord.custid
and customer.repid = emp.empno
group by name, area, phone, ename
UNION
select name as "NAME", area as "AREA", phone as "PHONE", ename as "SALES REP", min(sum(total)) as "TOTAL PURCHASES"
from demo.emp, demo.ord, demo.customer
where customer.custid = ord.custid
and customer.repid = emp.empno
group by name, area, phone, ename
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
