CODEWITHSUNDEEP
phpmysql
Parsa Pierre
Parsa Pierre

Reputation: 63

sum row content with matching columns

Yes yes, I know. The title is stupid and hard to understand.

Question:

There's a DB1 and a DB2.

DB1 has columns called id and price. id is a unique number for each item like 301 and so on. There's only one of each id in this DB.

DB2 contains a column named id and one named price. This DB has the duty of collecting data about each id ( item ) via a php script.

This means multiple rows with the same unique id.

a picture to demonstrate:

Multiple Rows

What i'm trying to do:

a script that querys an id ( matches the id with another table ) and sums up all the price columns with the same id.

I understand innerjoin

SELECT db1.id, db2.price
FROM db1
INNER JOIN db2
ON db1.id=db2.id;

but I want to sum all db2.price rows before showing. What can I do?

Upvotes: 2

Views: 56

Answers (2)

MrSimpleMind
MrSimpleMind

Reputation: 8607

What you want is this:

(I guess you want to show db1.price too (or other fields from db1, otherwise there is no meaning have this join)

SELECT db1.id, db1.price, db2s.price -- rest of db1 fields here
FROM db1
INNER JOIN (select id, sum(price) price from db2 group by id) as db2s
ON db1.id=db2s.id;

SQLFIDDLE

Upvotes: 1

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40919

I don't think you need a join for that. It should be enough to just do:

SELECT id, sum(price) as price_sum FROM db2 GROUP BY id

This should give you 2 columns:

  • id of the price
  • sum of all prices for given price ID

Upvotes: 0

Related Questions