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

Question

(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_ids 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.

score 1 · Accepted Answer · answered Dec 25 '15 at 20:17

1

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.

answered Dec 25 '15 at 20:17

Tuure

521
4
12

Thanks. How can I get by child_id? for example just child_id = 1 – John IP Dec 25 '15 at 20:30
A simple where should do it. Just add : `WHERE Child_id = 1` before the `GROUP BY`. ( Unrelated to this specific where clause, one thing to notice when filtering grouped results is the difference between WHERE and HAVING. https://stackoverflow.com/questions/9253244/sql-having-vs-where ) – Tuure Dec 25 '15 at 20:43
Thanks, and I have another question. How can I get all child_id from the first table (parent) by parent_id and use it as variable (to use for where) – John IP Dec 25 '15 at 20:53
If you join the parent table, you can then use all of the parent tables columns in the where clause: `WHERE parent.parent_id = 5` The whole query might be something like: `SELECT Child_id, SUM(paid_price) FROM paid LEFT JOIN parent ON paid.Child_id = parent.Child_id WHERE parent.parent_id = 5 GROUP BY paid.Child_id` – Tuure Dec 25 '15 at 21:14
Thank you very much, that is working, but I want this return list of child_id(s) with that parent_id (sum of them) – John IP Dec 25 '15 at 21:24
I'm not sure I understood what you want as the result. If you want the parent_id added to the result, you can just add it to the SELECT like so: `SELECT Child_id, parent_id, SUM(paid_price)` If you want to know, how many parents a child has or how many children a parent has, you can use the COUNT aggregate function. – Tuure Dec 25 '15 at 21:35
No, I am sorry that I am not clear. You know I want to list sum of `paid_price` (from `paid `table by `child_id`), by `parent_id`. for example a parent may have 10 child, and each of child may have some prices. I want a list from each child and its price by `parent_id` – John IP Dec 25 '15 at 21:40
So you want a query that takes a parent_id (for example 25) and returns all its children (1, 2, 4) and sums each childs paid_prices if there are more than one child? I think the query I posted earlier should do just this. `SELECT Child_id, SUM(paid_price) FROM paid LEFT JOIN parent ON paid.Child_id = parent.Child_id WHERE parent.parent_id = 25 GROUP BY paid.Child_id` should return two rows: 1, 6206000 and 2, 2500000. If you want the full list without summing the child prices you can just leave the SUM and GROUP BY out of the query. – Tuure Dec 25 '15 at 23:03
Really thank you, But this one again returns one row! – John IP Dec 26 '15 at 03:40

score 0 · Answer 2 · answered Dec 25 '15 at 20:15

0

I don't have an easy way to test this here at the moment but basically you need to join the two tables on child_id and then group by parent_id while applying the sum() aggregate function to paid_price. Something along those lines:

with J as( select P.parent_id,Q.paid_price from parent P
   inner join paid Q on P.Child_id=Q.Child_id)
select parent_id, sum(paid_price) from J group by parent_id

answered Dec 25 '15 at 20:15

Snake Pilsken

1

Your query says `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'J as( select P.parent_id,Q.paid_price from parent P inner join paid Q on P.C' at line 1` – John IP Dec 25 '15 at 20:26

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

2 Answers2