I used SQL sum groupby, why is it a total sum rather than a group-by-group sum?-CodePudding

example

CREATE TABLE book(
    title VARCHAR(100) PRIMARY KEY, 
    price INT NOT NULL
)

CREATE TABLE buyer(
    name VARCHAR(20) ,
    title VARCHAR(100),
    primary key(name,title),
    foreign key(title)
    references book
)

I will omit the code to input data

book data = {('a',100),('b',200),('c',100),('d',300)}
buyer data = {('kim','a'),('kim','b'),('ca','c'),('yan','d'),('yan','c')}

select name, sum(price) sum_price
from book,buyer
group by name

I want to know the total by name

but this result is the sum of all books.

I don't know what's the problem

CodePudding user response：

This is because you didn't make a proper join and as a result you ended up with a full outer join, which means each row of the book table will be joined with each row of the buyer table. Try to either add a where statement with correct join of the 2 tables, or use join syntax in the from part.

CodePudding user response：

Joining two tables without a join condition produces the full cartesian product of the two tables.

Try this, should solve your problem.

select name, sum(price) sum_price
from buyer br left join book bk on br.title=bk.title
group by name