CODEWITHSUNDEEP
sqlsql-server-2008
MVC_Nhibernate
MVC_Nhibernate

Reputation: 457

Sql Query to find sum from different tables

Hi i require a help in writing a query.

Tables are:

tblStandard1students
tblStandard2students
tblStandard1students
tblDivision
tblCandidateinfo

tblStandard1students,tblStandard2students,tblStandard1studentstbl contain information about students enrolled in standard 1,2 and 3

tblStandars1students

Candid  admitted
  1        Y
  2        N
  3        Y

tblDivision contains only 2 columns

 ID     Division
  1       A
  2       B
  3       C

tblCandidateinfo

Candid  gender Division
  1       M      1
  2       F      2

and so on...

Now I want the table like this

Division  Students(Standard1)  Students(Standard2)  Students(Standard3)
           M           F         M          F        M          F
------------------------------------------------------------------------
 A         1           0         0          0         0         1
 B         2           2         3          3         4         4
 C         1           0         0          0         0         0

I tried this following query:

SELECT Division,

    ( SELECT count(*)
     FROM tblStandard1students A
     INNER JOIN tblCandidateinfo B ON A.Candid=B.Candid
     INNER JOIN tblDivision C ON C.ID=B.Division) AS Students(Standard1),

    ( SELECT count(*)
     FROM tblStandard2students A
     INNER JOIN tblCandidateinfo B ON A.Candid=B.Candid
     INNER JOIN tblDivision C ON C.ID=B.Division) AS Students(Standard2),

    ( SELECT count(*)
     FROM tblStandard3students A
     INNER JOIN tblCandidateinfo B ON A.Candid=B.Candid
     INNER JOIN tblDivision C ON C.ID=B.Division ) AS Students(Standard3)
FROM tblDivision Z

but this is only half the query i din segregate it gender wise...help me to complete it.

Upvotes: 1

Views: 278

Answers (2)

shola
shola

Reputation: 704

SELECT divison.Division ,IFNULL(stander1.M,0),IFNULL(stander1.F,0) FROM test.tblDivision divison Left join (SELECT division ,count( case gender when 'M' then 1 else null end) as M,count( case gender when 'F' then 1 else null end) as F FROM test.tblCandidateinfo tc inner join test.tblStandars1students ts1

ON tc.Candid=ts1.Candid

group by division) as stander1 on stander1.division= divison.id

group by divison.id ;

Insted of IFNULL use ISNULL and take left join for all standar tables

Upvotes: 0

lc.
lc.

Reputation: 116438

;WITH combined AS
(
    SELECT ci.Division, 'Students(Standard1) ' + ci.gender AS grp
    FROM tblCandidateInfo ci
    INNER JOIN tblStandard1students s ON ci.Candid = s.Candid
    UNION ALL
    SELECT ci.Division, 'Students(Standard2) ' + ci.gender AS grp
    FROM tblCandidateInfo ci
    INNER JOIN tblStandard2students s ON ci.Candid = s.Candid
    UNION ALL
    SELECT ci.Division, 'Students(Standard3) ' + ci.gender AS grp
    FROM tblCandidateInfo ci
    INNER JOIN tblStandard1studentstbl s ON ci.Candid = s.Candid
)
SELECT Division, 
    [Students(Standard1) M], [Students(Standard1) F],
    [Students(Standard2) M], [Students(Standard2) F],
    [Students(Standard3) M], [Students(Standard3) F]
FROM
(
    SELECT d.Division, grp
    FROM tblDivision d
    LEFT OUTER JOIN combined c ON d.ID = c.Division
) x
PIVOT
(
    COUNT(grp)
    FOR grp IN ([Students(Standard1) M], [Students(Standard1) F],
        [Students(Standard2) M], [Students(Standard2) F],
        [Students(Standard3) M], [Students(Standard3) F])
) y
ORDER BY Division

Upvotes: 2

Related Questions