How to get no of enames from emp tables

Posted by Kk86kiran under Sql Server on 3/9/2012 | Points: 10 | Views : 937 | Status : [Member] | Replies : 7
I have 2 tables emp and dept. there is a foreign key relationship between these 2 tables.

In emp i have 3 fields and in dept 2 fields
emp: Dept
1.ename 1.Dname
2.eno 2.deptno
3.deptno

Now i want to retrieve Dname from Dept and no of enames from emp. No of enames means just count of enames only

How write a query for this

k.m.j.kiran


Responses

Posted by: Hmanjarawala on: 3/9/2012 [Member] Bronze | Points: 25

Up
0
Down
Just write down query:

Select b.dname, b.deptno, count(1) as noOfemp
From emp as a
inner join dept as b on a.deptno = b.deptno
group by b.deptno,b.deptname
order by b.deptno



Mark this as answer, if it helps you.....

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Kk86kiran, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kk86kiran on: 3/9/2012 [Member] Starter | Points: 25

Up
0
Down
i want ename from emp table not dname and also i want how many enames are available in the emp table.

k.m.j.kiran

Kk86kiran, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hmanjarawala on: 3/12/2012 [Member] Bronze | Points: 25

Up
0
Down
plz read your question carefully

Himanshu Manjarawala
Sr. Software Engineer@AutomationAnywhere
http://fieredotnet.wordpress.com/

Kk86kiran, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naraayanan on: 3/12/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
 Select Count(ename) from tbl_emptable 


Regards,
Lakshmi Naraayanan.S
http://dotnettechrocks.blogspot.in/
http://abaprocker.blogspot.com/

Kk86kiran, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 3/12/2012 [Member] Silver | Points: 25

Up
0
Down
The question is repeated
http://www.dotnetfunda.com/forums/thread8328-how-to-get-no-of-enames-from-emp-tables.aspx
Please don't post same question twice..


The Solution
--------------------
Try this

SELECT b.deptname,COUNT(a.ename) AS [No of Employees] 

FROM TableB b JOIN TableA a
ON b.deptno=a.deptno
GROUP BY b.deptname




Hope this will give the result...!!!

Kk86kiran, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/12/2012 [Member] Silver | Points: 25

Up
0
Down
-- using subquery

select *
,Employees=(select COUNT(eno) from emp where emp.deptno=dept.deptno)
from dept


Thanks,
Sanjay

Kk86kiran, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/12/2012 [Member] Silver | Points: 25

Up
0
Down
-- using left outer join
 select dept.deptName,COUNT(*) TotEmployees from 

dept left outer join emp
on dept.deptno =emp.deptno
group by emp.deptno , dept.deptName


Thanks,
Sanjay

Kk86kiran, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response