CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 952

Calculate in SQL with Inner Join

I am trying to calculate in a SQL statement. I am trying to calculate the total amount in the invoice.total column per customer. I created the following statement:

SELECT customers.firstname, customers.lastname, customers.status, SUM(invoice.total) AS total 
FROM customers
INNER JOIN invoice
ON customers.id=invoice.id;

When I run this, I get the total amount in the table. I have 15 different customers in this table but I get only the name of the first customer and the total amount of all customers. What am I doing wrong?

Upvotes: 2

Views: 2001

Answers (4)

dotnetom
dotnetom

Reputation: 24916

First, you need to Group the data when you want to have aggregate results:

SELECT customers.firstname, customers.lastname, customers.status, SUM(invoice.total) AS total 
FROM customers
INNER JOIN invoice
    ON customers.id=invoice.id
GROUP BY customers.firstname, customers.lastname, customers.status;

Second, are you sure you are joining the table by correct fields? Is invoice.id correct column? I would expect invoice.id to be primary key for the table and instead I would expect another column for foreign key, invoice.customerid for example. Please double check it is correct.

UPDATE: As it was mentioned in comments, if you have two customers with same first name, last name and status, the data will be grouped incorrectly. In that case you need to add unique field (e.g. customers.id) to SELECT and GROUP BY statements.

Upvotes: 2

HeinrichG
HeinrichG

Reputation: 11

You need to add a Group By with column names in your query.

Upvotes: 1

Sagar
Sagar

Reputation: 292

Use group by clause in your query or you can use common table expression to get the sum of records by customer and then make a inner join with customer table.

Upvotes: 1

Jakub Szumiato
Jakub Szumiato

Reputation: 1318

You have to add a group by customer (id property for example). If you want to have first and last name in select then you will have to group by them as well.

Upvotes: 2

Related Questions