Get the sum of authors' books with Mysql

Question

I’m starting with sql and I’m trying to get the sum of all books of each user in the cart table, but I’m not getting.

drop table if exists carrinho_de_compras;
drop table if exists usuario;
drop table if exists livro;

create table if not exists livro (
    id_livro bigint not null,
    nome varchar(20) not null,
    preco double not null,
    constraint pk_ID_Livro primary key(id_livro)
);

create table if not exists usuario (
    id_user smallint not null,
    nome varchar(40) not null,
    constraint pk_ID_User primary key(id_user)
);

create table if not exists carrinho_de_compras (
    id_user smallint not null,
    id_livro bigint not null,
    constraint fk_ID_User foreign key(id_user) references usuario(id_user),
    constraint fk_ID_Livro foreign key(id_livro) references livro(id_livro)
);

-- Insere os livros
insert into livro (id_livro, nome, preco) values 
(1, 'Chapeuzinho Vermelho', 4.20), 
(2, 'Os tres Porquinhos', 3.00),
(3, 'Branca de Neve', 3.50);

-- Criaos usuarios
insert into usuario (id_user, nome) 
    values (1, 'Joao da Silva'), (2, 'Pedro Pereira');

-- Adiciona no carrinho de compras
insert into carrinho_de_compras (id_user, id_livro) 
    values (1, 1), (1, 2), (2, 2);

select usuario.nome, sum(livro.preco) from usuario, livro
    inner join carrinho_de_compras as c on c.id_user = usuario.id_user
    and c.id_livro = livro.id_livro;

It returns the following error:

12:31:15 select usuario.nome, sum(livro.preco) from usuario, livro Inner Join carrinho_de_compras as c on c.id_user = usuario.id_user and c.id_livro = livro.id_livro LIMIT 0, 1000 Error Code: 1054. Unknown column 'usuario.id_user' in 'on clause' 0.000 sec

Answer 1

Use the query below:

select u.nome, sum(l.preco)
from usuario u, livro l, carrinho_de_compras as c 
where c.id_user = u.id_user and c.id_livro = l.id_livro
group by u.nome;

I removed the Inner Join and put the validation in the Where; the mysql was "getting lost" with the validation clause on.

Answer 2

To return the price spent by each user would be like this

SELECT 
usuario.nome, sum(l.preco) as totalGasto
from usuario
-- Juntar todas as compras do usuario
inner join carrinho_de_compras cdp 
    on cdp.id_user = usuario.id_user
-- Pegar a compra do usuario e ver os livros q teve nela
inner join livro l
    on l.id_livro = cdp.id_livro
group by usuario.id_user

Now you will need to do a separate query to return the books purchased by each user

Answer 3

As you want to bring quantity of purchases only from table cart_de_compras Voce can use command.

select id_user,sum(id_book) as qtd_books from cart_de_compras group by(id_user);

so it sums all id_book and sorts per user with the quantity on the side.

id_user| qty_books 1 | 3 2 | 2

Forehead and speak to me there.