CODEWITHSUNDEEP
mysqlsqlselectjoin
John IP
John IP

Reputation: 87

MySql: Getting sum of a table by the id of its parent from other table

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    ║                   
╚══════════╩═════════════════╩═══════╝

Here are my tables value:

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         ║100         ║
║══════════╬══════════╬════════════╣
║ 2        ║2         ║250         ║
║══════════╬══════════╬════════════╣
║ 3        ║1         ║35          ║
║══════════╬══════════╬════════════╣
║ 4        ║1         ║17          ║
╚══════════╩══════════╩════════════╝

I have a query, but that does not work the way I wanted.

This is my query:

SELECT parent.Child_id, parent.parent_id,  SUM( paid_price )
FROM paid
JOIN parent ON parent.Child_id = paid.Child_id
WHERE parent.parent_id =25

This just return one row! But I want this return (get) all child_id from parent table, then by that ids return sum paid_price of each of them.

Now the query that I have just return:

╔══════════╦══════════╦════════════╗
║ Child_id ║Parent_id ║ paid_price ║
╠══════════╬══════════╬════════════╣
║ 1        ║25        ║152         ║
╚══════════╩══════════╩════════════╝

But as you can see parent_id = 25 has some children, but the query that I wrote just return one of them (just the first one)! I want this return sum of paid_price of all that have parent_id number 25:

╔══════════╦══════════╦════════════╗
║ Child_id ║Parent_id ║ paid_price ║
╠══════════╬══════════╬════════════╣
║1         ║25        ║152         ║
║══════════╬══════════╬════════════╣
║2         ║25        ║250         ║
╚══════════╩══════════╩════════════╝

Please try query, I think it should be hard query to get what I need!

How should I change the query to get the required result? (I think I should change whole my query)

Upvotes: 1

Views: 277

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Your original query was almost right, but it was missing a GROUP BY clause:

SELECT parent.Child_id, SUM(paid.paid_price) AS paid_price
FROM parent
INNER JOIN paid ON parent.Child_id = paid.Child_id
WHERE parent.parent_id = 25
GROUP BY paid.Child_id

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35583

You need ALL non-aggregating columns in the group by clause (parent.Child_id, parent.parent_id). The reason you only got one row initially is because you had no group by clause at all.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE parent
    (`Child_id` int, `parent_id` int, `title` varchar(7))
;

INSERT INTO parent
    (`Child_id`, `parent_id`, `title`)
VALUES
    (1, 25, 'bla-bla'),
    (2, 25, 'bla-bla'),
    (3, 5, 'bla-bla'),
    (4, 25, 'bla-bla')
;


CREATE TABLE paid
    (`Paid_id` int, `Child_id` int, `paid_price` int)
;

INSERT INTO paid
    (`Paid_id`, `Child_id`, `paid_price`)
VALUES
    (1, 1, 100),
    (2, 2, 250),
    (3, 1, 35),
    (4, 1, 17)
;

Query 1:

SELECT parent.Child_id, parent.parent_id, sum(paid.paid_price)
FROM paid
JOIN parent ON parent.Child_id = paid.Child_id
WHERE parent.parent_id =25
group by parent.Child_id, parent.parent_id

Results:

| Child_id | parent_id | sum(paid.paid_price) |
|----------|-----------|----------------------|
|        1 |        25 |                  152 |
|        2 |        25 |                  250 |

Please note MySQL has a default server setting that permits "lazy" syntax when using GROUP BY. The unfortunate side effect of this is problems like this one. Always include ALL non-aggregating columns and you will avoid such issues. Please refer to https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

Upvotes: 1

Related Questions