John IP John IP - 4 years ago 79
SQL Question

writing query for joining and getting the sum of all rows in mySql

(Sorry for bad title and context, I am not native English speaker)

Imagine this is my database's tables:

Table #1: Parent
╔══════════╦═════════════════╦═══════╗
║Child_id ║Primary key - AI ║ int ║
╠══════════╬═════════════════╬═══════╣
║parent_id ║Index-Forign_key ║ int ║
║══════════╬═════════════════╬═══════╣
║title ║ - ║varchar║
╚══════════╩═════════════════╩═══════╝





Table #2: Paid
╔══════════╦═════════════════╦═══════╗
║Paid_id ║Primary key - AI ║ int ║
╠══════════╬═════════════════╬═══════╣
║child_id ║Index-Forign_key ║ int ║
║══════════╬═════════════════╬═══════╣
║paid_price║ - ║int ║
╚══════════╩═════════════════╩═══════╝


Now I want to have a query, that return sum of
paid_price
, by
parent_id


I mean the query get all
child_id
s from
parent
table, then by each
child_id
get sum of
paid_price
from
paid
table.

Imagine these are my tables' data:

Table: parent (data)
╔══════════╦══════════╦═══════╗
║ Child_id ║parent_id ║ title ║
╠══════════╬══════════╬═══════╣
║ 1 ║25 ║bla-bla║
║══════════╬══════════╬═══════╣
║ 2 ║25 ║bla-bla║
║══════════╬══════════╬═══════╣
║ 3 ║5 ║bla-bla║
║══════════╬══════════╬═══════╣
║ 4 ║25 ║bla-bla║
╚══════════╩══════════╩═══════╝

Table: paid (data)
╔══════════╦══════════╦════════════╗
║ Paid_id ║Child_id ║ paid_price ║
╠══════════╬══════════╬════════════╣
║ 1 ║1 ║1000000 ║
║══════════╬══════════╬════════════╣
║ 2 ║2 ║2500000 ║
║══════════╬══════════╬════════════╣
║ 3 ║1 ║3506000 ║
║══════════╬══════════╬════════════╣
║ 4 ║1 ║1700000 ║
╚══════════╩══════════╩════════════╝





I hope I could describe it well that what I need

*Note: This is not my database structure. I just wanted to have query. that was harder to write my real database's tables.

Answer Source

You can achieve that (if I understood correctly) by using GROUP BY and SUM()

https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html

You can easily get the sum of the paid_prices for each Child_id from the paid table with a query something like this:

SELECT Child_id, SUM(paid_price) FROM paid GROUP BY Child_id;

If you want to have the title in the same result, you can left join that with ON paid.Child_id = parent.Child_id as the join condition.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download