CODEWITHSUNDEEP
mysqljoininner-join
Ayaz Alavi
Ayaz Alavi

Reputation: 4835

MySQL query for summing values in another table

I have got a couple of tables with a parent child relationship. I want to apply a sum function to one column of child table and return it with all data of parent table for example.

Parent_table
ID, Date, Title

Child_table
ID, another_id, column_to_sum
//(ID is foreign key pointing to Parent_table)

Sample Data in Parent_table
1, 22-11-2010 00:00:00 , 'Some Title'
2, 13-11-2010 00:00:00 , 'Some Title 2'

Sample Data in Child_table
1, 1, 10
1, 2, 11
1, 8, 3
2, 5, 11
2, 8, 6

Output of the query should return all columns in parent_table with an additional column, that is, summing the values of column_to_sum in Child_table for each item in parent_table matched by ID.

How?

Upvotes: 3

Views: 6970

Answers (3)

Harijs Krūtainis
Harijs Krūtainis

Reputation: 1270

This does not exactly answer the question, but might help someone since this was my solution. (I know it's missing the JOIN) And have no idea about the efficiency of this solution on a large DB.

SELECT 
    Parent_table.*,
        (SELECT 
            SUM(Child_table.column_to_sum) 
        FROM Child_table 
        WHERE Child_table.id = Parent_table.id)
FROM Parent_table

Upvotes: 3

Pradeep Singh
Pradeep Singh

Reputation: 3634

    SELECT p.ID,
      p.Date,
      p.Title,
      SUM(c.column_to_sum) Total
    FROM Parent_Table p LEFT JOIN
       Child_Table c ON p.ID = c.ID
    GROUP BY p.ID

Upvotes: 3

Adriaan Stander
Adriaan Stander

Reputation: 166576

This is off the top of my head, but how about

SELET p.ID,
  p.Date,
  p.Title,
  SUM(c.column_to_sum) Total
FROM Parent_Table p INNER JOIN
   Child_Table c ON p.ID = c.ID
GROUP BY p.ID,
      p.Date,
      p.Title

Upvotes: 2

Related Questions