Obtain the sum of the authors' books with Mysql

Navigation
0

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

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 user.name, sum (book.preco) from user, book inner join shopping cart as c on c.id_user = user.id_user and c.id_livro = libro.id_livro LIMIT 0, 1000 Error Code : 1054. Unknown column 'user.id_user' in 'on clause' 0.000 sec

    
asked by anonymous 20.08.2018 / 17:38

3 answers

0

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

    
20.08.2018 / 17:53
0

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 where ; mysql was "getting lost" with validation in the on clause.

    
20.08.2018 / 17:48
0

Since you want to bring quantity of purchases only from the shopping cart table you can use the command.

select id_user, sum (id_user) as qtd_book (s) from cart_debt group by (id_user);

so it sums all id_lib and sorts per user with the amount of the side.

id_user | qtd_books 1 | 3 2 | 2

Test and talk to me.

    
21.08.2018 / 21:12
What is EOH in PHP? List in HTML table separated by category