Sum of MySQL column values

Question

Sum of MySQL column values

Navigation

#1 by (0 votes)

0

I have the following MySQL tables:

CREATE TABLE IF NOT EXISTS 'tcc2'.'catalogo' (
'idPHL' BIGINT(20) UNSIGNED NOT NULL,
'isbn' VARCHAR(15) NULL DEFAULT NULL,
'titulo' LONGTEXT ,
'autor' LONGTEXT NULL DEFAULT NULL,
'edicao' VARCHAR(45) NULL DEFAULT NULL,
'anoPublicacao' LONGTEXT NULL DEFAULT NULL,
'editora' LONGTEXT NULL DEFAULT NULL,
'qtdEx' INT(11) NULL DEFAULT NULL,
 PRIMARY KEY ('idPHL'));

CREATE TABLE IF NOT EXISTS 'tcc2'.'disciplina_livros' (
'livro' BIGINT(20) UNSIGNED NOT NULL,
'disciplina' VARCHAR(10) NOT NULL,
'tipoReferencia' INT(11) NOT NULL,
 PRIMARY KEY ('livro', 'disciplina'),
INDEX 'fk_catalogo_has_disciplina_disciplina1_idx' ('disciplina' ASC),
INDEX 'fk_catalogo_has_disciplina_catalogo1_idx' ('livro' ASC),
INDEX 'fk_disc_livro_tipo_idx' ('tipoReferencia' ASC),
CONSTRAINT 'fk_disc_disciplina'
FOREIGN KEY ('disciplina')
REFERENCES 'tcc2'.'disciplina' ('codDisciplina')
ON UPDATE CASCADE,
CONSTRAINT 'fk_disc_livro_tipo'
FOREIGN KEY ('tipoReferencia')
REFERENCES 'tcc2'.'tipoReferencia' ('idTipoRef')
ON UPDATE CASCADE,
CONSTRAINT 'fk_disciplina_catalogo'
FOREIGN KEY ('livro')
REFERENCES 'tcc2'.'catalogo' ('idPHL'));

CREATE TABLE IF NOT EXISTS 'tcc2'.'tipoReferencia' (
'idTipoRef' INT(11) NOT NULL AUTO_INCREMENT,
'descricao' VARCHAR(25) NOT NULL,
PRIMARY KEY ('idTipoRef'));

CREATE TABLE IF NOT EXISTS 'tcc2'.'disciplina' (
'codDisciplina' VARCHAR(10) NOT NULL,
'nome' VARCHAR(45) NOT NULL,
 PRIMARY KEY ('codDisciplina'));

I need to make an appointment where the name of the discipline, the title, the number of copies and the sum of the number of copies are returned. I'm trying the following:

select d.nome, ca.titulo, ca.qtdEx, sum(ca.qtdEx) from catalogo as ca, disciplina_livros as dl,
disciplina as d
where dl.disciplina = 'EDU140' and ca.idPHL = dl.livro and
dl.tipoReferencia = 1
group by ca.titulo;

The result is as below, the sum(ca.qtdEx) column should return 18, not 324.

Modeling:

How do I return the sum of the qtdEx column?

mysql

asked by anonymous 28.07.2018 / 22:34

1 answer

SCRIPT5009: 'Promise' is not defined Scroll Jquery html css

score 0 · Accepted Answer

Perhaps the return of the query being only the number of books in the stock of a particular discipline is a better way to do this query

Query

SELECT D.NOME AS DISCIPLINA ,sum(CA.qtdEx) AS QNT_LIVROS FROM
     CATALOGO CA,
     DISCIPLINA_LIVROS DL,
     DISCIPLINA D
WHERE
     CA.idPHL = DL.LIVRO AND 
     DL.DISCIPLINA = D.codDisciplina 
GROUP BY D.nome

Stock return example

+-------------+------------+
| DISCIPLINA  | QNT_LIVROS |
+-------------+------------+
| Matemática  |          1 |
| programação |         33 |
+-------------+------------+
2 rows in set (0.00 sec)

If you want, you can also find out how many books there are per discipline

Query

SELECT D.NOME AS DISCIPLINA ,count(CA.titulo) AS QNT_LIVROS FROM 
    CATALOGO CA, 
    DISCIPLINA_LIVROS DL, 
    DISCIPLINA D 
WHERE 
    CA.idPHL = DL.LIVRO AND 
    DL.DISCIPLINA = D.codDisciplina 
GROUP BY D.nome;

Return example

+-------------+------------+
| DISCIPLINA  | QNT_LIVROS |
+-------------+------------+
| Matemática  |          1 |
| programação |          2 |
+-------------+------------+
2 rows in set (0.00 sec)