How do I sum up the total of several grades?

Question

How do I sum up the total of several grades?

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

In my DB I did the search of a student and threw me the subjects that he / she has studied (24 subjects), qualification of each subject and the credits of each one (1 subject = 10 credits). Now, what I want is to add the credits and that they no longer appear to me individual but summed up in a single row (240 credits). my query at the moment

SELECT * FROM (SELECT Usuario.usuario,
              Usuario.actividad,
              Usuario.fktipousuario,
              Usuario.fkedousuario,
              Persona.paterno,
              Persona.materno,
              Persona.nombres,
              Persona.curp,
                      Mail.email,
                      Telefono.numtel as telefono,
                      Delegacion.nombre as delegacion,
                      Asignatura.modulo,
                      Asignatura.tipo,
                      Asignatura.creditos,
                      Asignatura.descripcionasig,
                      Inscripcion.calificacion,
                      Acta.fechaplat,
                      Grupo.grupo
        FROM usuario AS Usuario
        LEFT JOIN persona as Persona ON(Usuario.idusuario = Persona.fkusuario)
        LEFT JOIN direccion as Direccion ON(Persona.idpersona = Direccion.fkpersona)
        LEFT JOIN colonia as Colonia ON(Colonia.idcolonia = Direccion.fkcolonia)
        LEFT JOIN mail as Mail ON(Persona.idpersona = Mail.fkpersona)
        LEFT JOIN telefono as Telefono ON(Persona.idpersona = Telefono.fkpersona)
        LEFT JOIN delegacion as Delegacion ON(Colonia.fkdelegacion = Delegacion.iddelegacion), 
                  alumno AS Alumno, 
                  inscripcion AS Inscripcion, 
                  acta AS Acta, 
                  asignatura AS Asignatura, 
                  grupo AS Grupo 
        WHERE Usuario.idusuario = Alumno.fkusuario
          AND Inscripcion.fkalumno = Alumno.idalumno
          AND Inscripcion.fkgrupo = Grupo.idgrupo
          AND Inscripcion.fkacta = Acta.idacta
          AND Inscripcion.valida = 1 
          AND Acta.fkgrupo = Grupo.idgrupo
          AND Grupo.fkasignatura = Asignatura.idasignatura
          AND Asignatura.idasignatura NOT   IN (1,2,3)
              AND Usuario.usuario LIKE 'ead1410002'
            GROUP BY Asignatura.modulo DESC, 
                     Asignatura.creditos ASC, 
                     Acta.fechaplat DESC) Historial

mysql sql

asked by cmre1988 17.05.2016 в 02:32

source

2 answers

error with docker installed on a 32bit architecture machine Compare dates in php

score 2 · Answer 1

Making sum the credit and grouping by id you can do what you need, you would have to have the id in the subquery to be able to group by id in the main query, something like this ....

    SELECT SUM(creditos) AS Credito, idpersona, nombres FROM (SELECT Usuario.usuario,
              Usuario.actividad,
              Usuario.fktipousuario,
              Usuario.fkedousuario,
              Persona.paterno,
              Persona.materno,
              Persona.nombres,
              Persona.curp,
              Persona.idpersona,
                      Mail.email,
                      Telefono.numtel as telefono,
                      Delegacion.nombre as delegacion,
                      Asignatura.modulo,
                      Asignatura.tipo,
                      Asignatura.creditos,
                      Asignatura.descripcionasig,
                      Inscripcion.calificacion,
                      Acta.fechaplat,
                      Grupo.grupo
        FROM usuario AS Usuario
        LEFT JOIN persona as Persona ON(Usuario.idusuario = Persona.fkusuario)
        LEFT JOIN direccion as Direccion ON(Persona.idpersona = Direccion.fkpersona)
        LEFT JOIN colonia as Colonia ON(Colonia.idcolonia = Direccion.fkcolonia)
        LEFT JOIN mail as Mail ON(Persona.idpersona = Mail.fkpersona)
        LEFT JOIN telefono as Telefono ON(Persona.idpersona = Telefono.fkpersona)
        LEFT JOIN delegacion as Delegacion ON(Colonia.fkdelegacion = Delegacion.iddelegacion), 
                  alumno AS Alumno, 
                  inscripcion AS Inscripcion, 
                  acta AS Acta, 
                  asignatura AS Asignatura, 
                  grupo AS Grupo 
        WHERE Usuario.idusuario = Alumno.fkusuario
          AND Inscripcion.fkalumno = Alumno.idalumno
          AND Inscripcion.fkgrupo = Grupo.idgrupo
          AND Inscripcion.fkacta = Acta.idacta
          AND Inscripcion.valida = 1 
          AND Acta.fkgrupo = Grupo.idgrupo
          AND Grupo.fkasignatura = Asignatura.idasignatura
          AND Asignatura.idasignatura NOT   IN (1,2,3)
              AND Usuario.usuario LIKE 'ead1410002'
            GROUP BY Asignatura.modulo DESC, 
                     Asignatura.creditos ASC, 
                     Acta.fechaplat DESC) Historial
GROUP BY idpersona, nombres

score 0 · Answer 2

Hi, convert your Sub Query in your Query principal, try this:

SELECT Usuario.usuario,
    Usuario.actividad,..,
    SUM(Asignatura.creditos)  
        OVER(PARTITION BY Usuario.usuario 
                ORDER BY Usuario.usuario) AS [CreditosXUsr],
    Asignatura.descripcionasig,
    Inscripcion.calificacion,
    Acta.fechaplat,
    Grupo.grupo
FROM .....

Only works in% co_of% or later