Mysql Select Multiple query

Question

Mysql Select Multiple query

Navigation

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

1

How about, I have the following table:

What I want to do is a table like the following:

Where I show the number of students per semester in each career, in my table History, there are more than one student with the same id because that table refers to the subjects and how each student has several subjects and Each subject belongs to a semester:

I have the following query that gives me the highest semester of the student, that this is my semester that I want to consult:

SELECT idAlum, MAX(semes) as Semestre FROM Historial
GROUP BY idAlum;

And I have this other query that gives me the highest semester per race, but it is bad because if I have 2 students of the same race, only take into account the highest.

SELECT idPro, MAX(Semes) as Semestre FROM Historial
GROUP BY idPro;

I think I have to make a select within another select but I have not been able to resolve that part.

Beforehand, thank you.

mysql-workbench

asked by Gael Rodriguez 30.05.2018 в 02:44

source

2 answers

How to show records of 2 related tables? help with entityframework query when showing relationships

score 1 · Answer 1

Using subqueries you can reach the goal. For each column a subquery is being created that recovers the amounts for each semester. Depending on the semesters you may have you must add the "sub-blocks".

SELECT carreras.idPro as carrera, 
(
    SELECT count(*) FROM Historial
    WHERE Historial.idPro = carreras.idPro
    AND Semes = '1er Semestre'
) as 1erSemestre,
(
    SELECT count(*) FROM Historial
    WHERE Historial.idPro = carreras.idPro
    AND Semes = '2do Semestre'
) as 2doSemestre,
(
    SELECT count(*) FROM Historial
    WHERE Historial.idPro = carreras.idPro
    AND Semes = '3er Semestre'
) as 3erSemestre,
(
    SELECT count(*) FROM Historial
    WHERE Historial.idPro = carreras.idPro
    AND Semes = '4to Semestre'
) as 4toSemestre,
(
    SELECT count(*) FROM Historial
    WHERE Historial.idPro = carreras.idPro
    AND Semes = '5to Semestre'
) as 5toSemestre,
(
    SELECT count(*) FROM Historial
    WHERE Historial.idPro = carreras.idPro
    AND Semes = '6to Semestre'
) as 6toSemestre,
(
    SELECT count(*) FROM Historial
    WHERE Historial.idPro = carreras.idPro
    AND Semes = '8vo Semestre'
) as 8voSemestre,
(
    SELECT count(*) FROM Historial
    WHERE Historial.idPro = carreras.idPro
    AND Semes = '9no Semestre'
) as 9voSemestre
from (
    SELECT DISTINCT idPro FROM Historial
) as carreras

score 0 · Answer 2

I think your problem is in the design of your database and not in your queries, I make a system for managing students and get the number of students per semester, subject, career .. does not have to be so complicated, take a look at see if what you need you can get easier by changing the structures of your tables (or creating new ones)