How to get no of enames from emp tables

Posted by Kk86kiran under Sql Server on 3/10/2012 | Points: 10 | Views : 884 | Status : [Member] | Replies : 6
I HAVE 2 TABLES IN THAT I HAVE 3 FIELDS IN 1ST TABLE & 2 FIELDS IN 2ND TABLE.

table A consists ename,eno,deptno
table B consists deptno,deptname

there is a foreign key relation ship between table A & table B

i want dnames from table B and no. of enames from table A . based on deptno

I want only count of enames.

k.m.j.kiran


Responses

Posted by: Denny on: 3/10/2012 [Member] Starter | Points: 25

Up
0
Down
Please check this query

SELECT COUNT(A.ename) AS NoofEnames
,B.deptname AS DepartmentName
FROM
A INNER JOIN
B
ON
A.deptno=B.deptno
GROUP BY deptname


Regards,
Denny Kurian

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

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

Up
0
Down
i didnt get ans with your query. i am getting error with that

k.m.j.kiran

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

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

Up
0
Down
Hi,
Try this

select COUNT(ename) ename,deptName from emp e,dept d

where d.deptno=e.deptno
group by d.deptno,d.deptName


Thanks,
Sanjay

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
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: Hmanjarawala on: 3/12/2012 [Member] Bronze | Points: 25

Up
0
Down
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: Denny on: 3/13/2012 [Member] Starter | Points: 25

Up
0
Down
Check this one

SELECT COUNT(A.ename) AS NoofEnames
,B.deptname AS DepartmentName
FROM
A INNER JOIN
B
ON
A.deptno=B.deptno
GROUP BY deptname

Regards,
Denny Kurian

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

Login to post response