Sum of Mysql column values

Question

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 a query where the name of the discipline is returned, the title, the number of copies and the sum of the number of copies. 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 column sum(ca.qtdEx) should return 18 and not 324.

Modeling:

How do I return the sum of the qtdEx column?

Answer 1

Perhaps the return of the query being only the number of books in the stock of a given discipline is a better way to perform 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

Example of stock return

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

If you want you can also know how many books there are by 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)