CODEWITHSUNDEEP
mysqlsql
richard
richard

Reputation: 547

Mysql sum using a group by on one field while also considering another field

I have a joined table from which I want to sum the attribute amounts and also group by name.

joined_tb
name        attr        amount
billy       'attr1'     2
billy       'attr2'     4
billy       'attr1'     7
billy       'attr3'     8
jean        'attr2'     6
jean        'attr1'     1
jean        'attr2'     11

This table is a result of a join between these two tables using id

t1                  t2
id  name            id    attr      amount
1   billy           1     'attr1'   2
2   jean            1     'attr2'   4
                    1     'attr1'   7
                    1     'attr3'   8
                    2     'attr2'   6
                    2     'attr1'   1
                    2     'attr2'   11

I want to end up with this table:

result_tb
name        attr        amount
billy       'attr1'     9
billy       'attr2'     4
billy       'attr3'     8
jean        'attr1'     1
jean        'attr2'     17

I am able to sum by attr, but it ignores name. How can I get result_tb from t1 and t2?

Upvotes: 0

Views: 322

Answers (2)

user319198
user319198

Reputation:

Try Below:

SELECT t1.name, t2.attr, sum(t2.amount) as totalamount
FROM table1 as t1 LEFT JOIN table2 as t2 on t1.id = t2.id
group by t1.name, t2.attr

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

Try this:

select t1.name, t2.attr, sum(t2.amount) from t1
join t2 on t1.id = t2.id
group by t1.name, t2.attr

Upvotes: 3

Related Questions