Count in multiple tables

Question

Count in multiple tables

Navigation

#1 by (1 votes)

0

I have 3 tables

Ineedaquerythattakesthenameoftheschool,amountofclassestypeA,qtdeofclassestypeB,qtdeofstudentswhoareinclassestypeA,andqtdeofstudentswhoareinclassestypeB.>

Sameasthisexample:

I tried to do

Select E.NOME,
SUM(CASE WHEN C.TIPO = 4 THEN 1 ELSE 0 end) as CLTELE5A8,
SUM(CASE WHEN C.TIPO = 5 THEN 1 ELSE 0 end) as CLTELEMED,

But I do not know how I will do the student count

mysql sql select count

asked by anonymous 14.08.2017 / 19:03

1 answer

How to create a Dropdown Multi-Level menu using HTML? How to let my WebView restart automatically every 30 m

score 1 · Answer 1

The simplest way to implement is through subselect . If this gives you a performance problem, you can try using a temporary table .

Select E.NOME,
    SUM(CASE WHEN C.TIPO = 4 THEN 1 ELSE 0 end) as CLTELE5A8,
    SUM(CASE WHEN C.TIPO = 5 THEN 1 ELSE 0 end) as CLTELEMED,
    (SELECT COUNT(1) FROM ALUNOS AA JOIN CLASSES CC ON CC.COD_CLASSE = AA.CODCLASSE WHERE CC.TIPO = 'A') as 'QTD_ALUNOS_A',
    (SELECT COUNT(1) FROM ALUNOS AA JOIN CLASSES CC ON CC.COD_CLASSE = AA.CODCLASSE WHERE CC.TIPO = 'A') as 'QTD_ALUNOS_B'