Count in multiple tables

Asked

Viewed 166 times

0

I have 3 tables

Tab1 tab2

I need a query that takes the name of the school, number of classes type A, Qtde of classes type B, Qtde of students who are in classes type A, and Qtde of students who are in classes type B.

Like this example:

inserir a descrição da imagem aqui

I tried to make 

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 don’t know how I’m going to count students

mysql sql select count

  • It is a simplification of the original tables.

  • Start to make a solution, I am very Arrado. Maybe you can finish: Sqlfiddle

  • IS MySQL same or SQL Server as in your other questions?

  • SQL Server, I put mysql because I didn’t think it would change the solution syntax a lot

1 answer

1

The simplest way to implement is through subselect. In case it gives you any problem performance, can try to use 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'

Browser other questions tagged mysql sql select count

You are not signed in. Login or sign up in order to post.