how to get the count in SQL Server?

Question

I have tried a lot to figure how to get the count from two tables with respect to master table

I have three tables

enter image description here

Using these table values I need to get this output..

enter image description here

Tried but could get the desired result http://en.wikipedia.org/wiki/Join_(SQL) SQL - LEFT OUTER JOIN and WHERE clause http://forums.devshed.com/oracle-development-96/combination-of-left-outer-join-and-where-clause-383248.html

I am trying the given solution, please give me a moment to mark the correct answer and vote up.. what make you guys to downgrade? — AnandMohanAwasthi
– AnandMohanAwasthi, Commented Nov 25, 2011 at 8:58
@AnandMohanAwasthi: There are many users that post a question without showing what they tried. many of them have not tried anything at all. I'm not saying that this is the case with you but if you don't show what you did, we can't be sure (and some will assume you just want others to do the job for you). — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Nov 25, 2011 at 9:02
@ypercube: I was bit busy in trying the provided solution, check my profile you can easily figure that this is my third question . I want to say that before writing or asking question i do lot reseach, anyways I think I have found the solution.. — AnandMohanAwasthi
– AnandMohanAwasthi, Commented Nov 25, 2011 at 9:09

ypercubeᵀᴹ · Accepted Answer · 2011-11-25 08:51:16Z

4

You have to first GROUP BY in subqueries, then JOIN to the main table:

SELECT
    a.AttributeId     
  , COALECSE(cntE, 0) AS cntE
  , COALECSE(cntM, 0) AS cntM
FROM
    AttributeMaster AS a
  LEFT JOIN
    ( SELECT
          AttributeId
        , COUNT(*) AS cntE
      FROM 
          EmployeeMaster 
      GROUP BY
          AttributeId
    ) em
      ON  em.AttributeId = a.AttributeId
  LEFT JOIN
    ( SELECT
          AttributeId
        , COUNT(*) AS cntM
      FROM 
          MonthlyDerivedMaster 
      GROUP BY
          AttributeId
    ) mdm
      ON mdm.AttributeId = a.AttributeId

answered Nov 25, 2011 at 8:51

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Utku Yıldırım · Accepted Answer · 2011-11-25 08:58:42Z

0

SELECT AttributeId,
(SELECT COUNT(Eid) FROM EmployeeMaster WHERE AttributeMaster.AttributeId = EmployeeMaster.AttributeId) as master_eid,
(SELECT COUNT(Eid) FROM MonthnlyDerivedMaster WHERE AttributeMaster.AttributeId = MonthnlyDerivedMaster.AttributeId) as monthly_eid
FROM AttributeMaster

answered Nov 25, 2011 at 8:58

Utku Yıldırım

2,27716 silver badges20 bronze badges

Collectives™ on Stack Overflow

how to get the count in SQL Server?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related