OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Subquery

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Subquery [message #1671]

Tue, 21 May 2002 11:10

ckr
Messages: 13
Registered: May 2002

Junior Member

hai,

I have two tables Orders and Customers which are related by Cust_id.here i just want to know the total no of orders placed by each customer.

I used the query

select cust_name,cust_state,
(select count(*)
from orders
where orders.cust_id=customers.cust_id) as orders
from customers
order by cust_name;

the result is 'missing expression ' at subquery.

is there anything wrong with this query

please help me

Report message to a moderator

Re: Subquery [message #1672 is a reply to message #1671]

Tue, 21 May 2002 12:01

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

i think this may help you. i have used the standard oracle demo tables emp, and dept which are related by deptno.
query gives the output, number of employees in each location.
SQL> GET QQ
1 select b.loc,count(a.empno) from dept b, emp a
2 where b.deptno=a.deptno
3* group by b.loc
SQL> /

LOC COUNT(A.EMPNO)
------------- --------------
CHICAGO 6
DALLAS 5
NEW YORK 3

Report message to a moderator

Re: Subquery [message #1706 is a reply to message #1671]

Thu, 23 May 2002 13:54

pavani
Messages: 32
Registered: April 2002

Member

u have used a group function-count here so it's better to use a group by clause

Report message to a moderator

Re: Subquery [message #1742 is a reply to message #1671]

Sat, 25 May 2002 08:05

N. Rammohan
Messages: 5
Registered: May 2002

Junior Member

you can use this simple querry

select cust_name,cust_state, count(*)
from orders o,customers c
where o.cust_id=c.cust_id
group by cust_name,cust_stat
order by cust_name;

Report message to a moderator

Previous Topic:	inserting data into single table from multiple tables
Next Topic:	Joins in 8.1.6

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 07:26:29 CDT 2024