sum values and boxes after the comma

Question

sum values and boxes after the comma

Asked 10 years, 2 months ago

Viewed 254 times

0

Good morning guys, I’m having a little problem here and I’d like a little help from you, I have a select here that brings the workload of one student per subject, for each student it repeats the 2x classes and the workload different so I can’t group, how do I add them per student and also remove the amount of houses after the comma, is bringing 6 houses and need only bring 2 houses. follows the sql code:

SELECT C.EDEMPRESA_ID
    ,C.GMUNICIPIO_ID
    ,C.SECRETARIA_ID
    ,C.MATRICULA_ID
    ,F.FICHAS_ID
    ,F.FICHA
    ,E.ESCOLA_ID
    ,E.ESCOLA
    ,S.SERIE_ID
    ,S.SERIE
    ,TN.TURNO_ID
    ,TN.TURNO
    ,TM.TURMA_ID
    ,TM.TURMA
    ,M.MATERIA_ID
    ,M.MATERIA
    ,MAX(FC.FUNCIONA) AS FUNCIONA
    ,ISNULL(L.GLEG_CFH_ID, 0) AS GLEG_CFH_ID
    ,L.SIGLA
    ,L.LEG_CFH
    ,'Anual' AS BIMESTRE
    ,C.ANO
    ,MAX(E.ENDERECO) AS ENDERECO
    ,MAX(FL.PAI) AS PAI
    ,MAX(FL.MAE) AS MAE
    ,MAX(CONVERT(VARCHAR(10), F.NASCTO, 103)) AS DATANASCIMENTO
    ,MAX(GM.MUNICIPIO) AS GMUNICIPIO_ID_NASC
    ,MAX(E.FONE) AS FONE
    ,MAX(E.CEP) AS CEP
    ,C.AVALIACAO
    ,CASE 
        WHEN I.SITU_FINAL IS NULL
            THEN 'Cursando'
        WHEN I.SITU_FINAL = '0'
            THEN 'Cursando'
        WHEN I.SITU_FINAL = '1'
            THEN 'Tr. Turma'
        WHEN I.SITU_FINAL = '2'
            THEN 'Transferido'
        WHEN I.SITU_FINAL = '3'
            THEN 'Transferido'
        WHEN I.SITU_FINAL = '4'
            THEN 'Desistência'
        WHEN I.SITU_FINAL = '5'
            THEN 'Matrícula Sem Efeito'
        WHEN I.SITU_FINAL = '6'
            THEN 'Reclassificação'
        WHEN I.SITU_FINAL = '7'
            THEN 'Cassificação'
        WHEN I.SITU_FINAL = '8'
            THEN 'Falecido'
        WHEN I.SITU_FINAL = 'E'
            THEN 'Em Construção'
        WHEN I.SITU_FINAL = 'S'
            THEN 'Aprovado'
        WHEN I.SITU_FINAL = 'N'
            THEN 'Reprovado'
        WHEN I.SITU_FINAL = 'D'
            THEN 'Desistente'
        END SITUACAO,
                    (
                SELECT COUNT(*) AS DIAS_LETIVO
                FROM MONTA_DIA_LETIVO_RA(1, 5103254, 10, '2014', '03/20/2014', CONVERT(VARCHAR(10), GETDATE(), 101))
                ) AS DIAS_LETIVOS_TOTAIS
    ,(
            (
                SELECT COUNT(*) AS DIAS_LETIVO
                FROM MONTA_DIA_LETIVO_RA(1, 5103254, 10, '2014', '03/20/2014', CONVERT(VARCHAR(10), GETDATE(), 101))
                ) * (
                SELECT COALESCE(CAST(gm1.CARGA_HORA AS INTEGER), 0)
                FROM ED_MATRICUL M
                WHERE M.EDEMPRESA_ID = C.EDEMPRESA_ID
                    AND M.GMUNICIPIO_ID = C.GMUNICIPIO_ID
                    AND M.SECRETARIA_ID = C.SECRETARIA_ID
                    AND M.ANO = C.ANO
                    AND M.MATRICULA_ID = C.MATRICULA_ID
                ) * 100
            ) / (
            COALESCE(CAST((
                        SELECT (COALESCE(SUM(GM1.CARGA_HORA), 0))
                        FROM ED_GRA_MATE GM1
                        WHERE GM1.EDEMPRESA_ID = TM.EDEMPRESA_ID
                            AND GM1.GMUNICIPIO_ID = TM.GMUNICIPIO_ID
                            AND GM1.SECRETARIA_ID = TM.SECRETARIA_ID
                            AND GM1.GRADE_ID = TM.GRADE_ID
                        ) AS INTEGER), 0) / 100
            ) * COALESCE((
                SELECT (COALESCE(SUM(GM2.SEMI_HORA), 0) / 5)
                FROM ED_GRA_MATE GM2
                WHERE GM2.EDEMPRESA_ID = TM.EDEMPRESA_ID
                    AND GM2.GMUNICIPIO_ID = TM.GMUNICIPIO_ID
                    AND GM2.SECRETARIA_ID = TM.SECRETARIA_ID
                    AND GM2.GRADE_ID = TM.GRADE_ID
                ), 0)  AS CARGA_HORARIA
FROM ED_CAN_CFH AS C
LEFT JOIN ED_MATERIAS AS M ON C.EDEMPRESA_ID = M.EDEMPRESA_ID
    AND C.GMUNICIPIO_ID = M.GMUNICIPIO_ID
    AND C.SECRETARIA_ID = M.SECRETARIA_ID
    AND C.MATERIA_ID = M.MATERIA_ID
LEFT JOIN GLEG_CFH AS L ON C.GLEG_CFH_ID = L.GLEG_CFH_ID
LEFT JOIN ED_MATRICUL AS MT ON C.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND C.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND C.SECRETARIA_ID = MT.SECRETARIA_ID
    AND C.ANO = MT.ANO
    AND C.MATRICULA_ID = MT.MATRICULA_ID
INNER JOIN ED_FICHA AS F ON C.EDEMPRESA_ID = F.EDEMPRESA_ID
    AND C.GMUNICIPIO_ID = F.GMUNICIPIO_ID
    AND C.SECRETARIA_ID = F.SECRETARIA_ID
    AND F.FICHAS_ID = MT.FICHAS_ID
LEFT JOIN ED_SERIES AS S ON S.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND S.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND S.SECRETARIA_ID = MT.SECRETARIA_ID
    AND S.SERIE_ID = MT.SERIE_ID
LEFT JOIN ED_TURNO AS TN ON TN.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND TN.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND TN.SECRETARIA_ID = MT.SECRETARIA_ID
    AND TN.TURNO_ID = MT.TURNO_ID
LEFT JOIN ED_TURMAS AS TM ON TM.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND TM.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND TM.SECRETARIA_ID = MT.SECRETARIA_ID
    AND TM.TURMA_ID = MT.TURMA_ID
    AND TM.ANO = MT.ANO
    AND TM.TURNO_ID = MT.TURNO_ID
    AND TM.SERIE_ID = MT.SERIE_ID
    AND TM.ESCOLA_ID = MT.ESCOLA_ID
LEFT JOIN ED_ESCOLAS AS E ON E.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND E.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND E.SECRETARIA_ID = MT.SECRETARIA_ID
    AND E.ESCOLA_ID = MT.ESCOLA_ID
INNER JOIN ED_FILIACAO AS FL ON FL.EDEMPRESA_ID = F.EDEMPRESA_ID
    AND FL.GMUNICIPIO_ID = F.GMUNICIPIO_ID
    AND FL.SECRETARIA_ID = F.SECRETARIA_ID
    AND FL.FILIACAO_ID = F.FILIACAO_ID
LEFT JOIN GMUNICIPIO AS GM ON GM.GMUNICIPIO_ID = F.GMUNICIPIO_ID_NASC
LEFT JOIN ED_LOTACAO AS LT ON LT.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND LT.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND LT.SECRETARIA_ID = MT.SECRETARIA_ID
    AND LT.ESCOLA_ID = MT.ESCOLA_ID
    AND LT.ANO = MT.ANO
    AND LT.TURMA_ID = MT.TURMA_ID
    AND LT.TURNO_ID = MT.TURNO_ID
    AND LT.SERIE_ID = MT.SERIE_ID
LEFT JOIN ED_PESSOAL AS P ON P.EDEMPRESA_ID = LT.EDEMPRESA_ID
    AND P.GMUNICIPIO_ID = LT.GMUNICIPIO_ID
    AND P.SECRETARIA_ID = LT.SECRETARIA_ID
    AND P.ANO_LETIVO = LT.ANO
LEFT JOIN ED_PROFESSO AS PR ON PR.EDEMPRESA_ID = LT.EDEMPRESA_ID
    AND PR.GMUNICIPIO_ID = LT.GMUNICIPIO_ID
    AND PR.SECRETARIA_ID = LT.SECRETARIA_ID
    AND PR.PROFESSOR_ID = LT.PROFESSOR_ID
LEFT JOIN ED_FUNCIONA AS FC ON FC.EDEMPRESA_ID = PR.EDEMPRESA_ID
    AND FC.GMUNICIPIO_ID = PR.GMUNICIPIO_ID
    AND FC.SECRETARIA_ID = PR.SECRETARIA_ID
    AND FC.FUNCIONA_ID = PR.FUNCIONA_ID
LEFT JOIN ED_FIC_NOT N ON N.EDEMPRESA_ID = C.EDEMPRESA_ID
    AND N.SECRETARIA_ID = C.SECRETARIA_ID
    AND N.GMUNICIPIO_ID = C.GMUNICIPIO_ID
    AND N.MATRICULA_ID = MT.MATRICULA_ID
    AND N.ANO = MT.ANO
    AND N.ESCOLA_ID = MT.ESCOLA_ID
    AND N.MATERIA_ID = C.MATERIA_ID
LEFT JOIN ED_FIC_IND I ON I.EDEMPRESA_ID = C.EDEMPRESA_ID
    AND I.SECRETARIA_ID = C.SECRETARIA_ID
    AND I.GMUNICIPIO_ID = C.GMUNICIPIO_ID
    AND I.MATRICULA_ID = MT.MATRICULA_ID
    AND I.ANO = MT.ANO
    AND I.ESCOLA_ID = MT.ESCOLA_ID
    AND I.SERIE_ID = LT.SERIE_ID
    AND I.TURNO_ID = LT.TURNO_ID
    AND I.TURMA_ID = LT.TURMA_ID
LEFT JOIN ED_GRA_MATE GM1 ON GM1.EDEMPRESA_ID = N.EDEMPRESA_ID
    AND GM1.SECRETARIA_ID = N.SECRETARIA_ID
    AND GM1.GMUNICIPIO_ID = N.GMUNICIPIO_ID
    AND GM1.MATERIA_ID = N.MATERIA_ID
WHERE C.EDEMPRESA_ID = 1
    AND C.GMUNICIPIO_ID = 5103254
    AND C.SECRETARIA_ID = 10
    AND MT.ANO = '2014'
    AND LT.ESCOLA_ID = 1
    AND LT.SERIE_ID = 6
    AND LT.TURNO_ID = 1
    AND LT.TURMA_ID = 1
    AND C.BIMESTRE IN (7)
GROUP BY C.EDEMPRESA_ID
    ,C.GMUNICIPIO_ID
    ,C.SECRETARIA_ID
    ,C.MATRICULA_ID
    ,F.FICHAS_ID
    ,F.FICHA
    ,E.ESCOLA_ID
    ,E.ESCOLA
    ,S.SERIE_ID
    ,S.SERIE
    ,TN.TURNO_ID
    ,TN.TURNO
    ,TM.TURMA_ID
    ,TM.TURMA
    ,C.FALTAS
    ,M.MATERIA_ID
    ,M.MATERIA
    ,L.GLEG_CFH_ID
    ,L.SIGLA
    ,L.LEG_CFH
    ,C.ANO
    ,C.BIMESTRE
    ,C.AVALIACAO
    ,I.SITU_FINAL
    ,I.CARGA
    ,TM.EDEMPRESA_ID
    ,TM.GMUNICIPIO_ID
    ,TM.SECRETARIA_ID
    ,TM.GRADE_ID
    ,MT.DATA
    ,N.FALTAS
    ,GM1.CARGA_HORA

Thanks in advance.

thank you.

Because GM1.CARGA_HORA is in the group by?

– Fernando

2014/09/19 at 12:45
In fact I confess that I did not understand it, but if I turn the select without it being in the group by it of error, only works with it in group by...

– StoneSour

2014/09/19 at 12:47
What error occurs?

– Fernando

2014/09/19 at 12:48
In fact not of the error, it happens the following, the result brings me the charge horaria by matter for example, if I run this select brings me the load horaria so 1092000.000000, first I would like to know how to leave only 2 houses after the comma and 2º that he is duplicating the materials because the hourly charge are different, so I wanted to sojourn them per student, the general sum of the hourly load of all materials

– StoneSour

2014/09/19 at 12:49
It is that if you group by workload the workload will not be added up. That is, for each value it will unfold in another record.

– Fernando

2014/09/19 at 12:51
yes but if I put the SUM() it gives me the error Cannot perform an Aggregate Function on an Expression containing an Aggregate or a subquery.

– StoneSour

2014/09/19 at 12:52

Show 1 more comment

1 answer

Browser other questions tagged database sql-server

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

by Fernando • **336** points · Answer 1 · 2014-09-19T12:54:12+00:00

To sum the time loads use the SUM().

To format the decimals you can try: CONVERT(DECIMAL(10,2), [COLUMN])

And theoretically as far as I understood, take the GM1.CARGA_HORA from GROUP BY.

But as the carga_horaria column is calculated the error will occur: "Cannot perform an Aggregate Function on an Expression containing an Aggregate or a subquery". So you have some alternatives to make:

 1. Fazer deste SELECT um SUB-SELECT, ou seja, montar um SELECT do resultado deste (agrupado por carga_hora) e neste novo select fazer o SUM().
 2. Criar uma view / tabela temporária com este select e obter o resultado a partir desta view/tabela temporária.