CODEWITHSUNDEEP
mysqlsql
Donny Akhmad Septa Utama
Donny Akhmad Septa Utama

Reputation: 823

Select Join table with table self

i have question here...

Table : history
|id   |transaction|created_at         |merchant_id|
|-----|-----------|-------------------|-----------|
|1    |400        |2015-10-12 11:08:37|33         |
|1    |500        |2015-10-15 09:38:22|33         |
|1    |600        |2015-10-21 14:47:12|22         |
|2    |100        |2015-09-26 10:48:27|31         |
|2    |500        |2015-09-30 11:18:07|27         |
|2    |300        |2015-10-02 17:33:57|31         |

i want when im do query:

SELECT SUM(a.transaction)/COUNT(a.transaction) AS avg_trans
FROM history AS a GROUP BY a.id, a.merchant_id

Result:
|id    |avg_trans|merchant_id|
|------|---------|-----------|
|1     |450      |33         |
|1     |600      |22         |
|2     |200      |31         |
|2     |500      |27         |

then show avg_trans into table history, like this :

|id   |transaction|created_at         |avg_trans|merchant_id|
|-----|-----------|-------------------|---------|-----------|
|1    |400        |2015-10-12 11:08:37|450      |33         |
|1    |500        |2015-10-15 09:38:22|450      |33         |
|1    |600        |2015-10-21 14:47:12|600      |22         |
|2    |100        |2015-09-26 10:48:27|200      |31         |
|2    |200        |2015-09-30 11:18:07|500      |27         |
|2    |300        |2015-10-02 17:33:57|200      |31         |

anyone can help me?

Upvotes: 2

Views: 34

Answers (2)

Kay
Kay

Reputation: 413

 select history.*, derived_table.avg_trans from history left join (SELECT id, merchant_id, SUM(a.transaction)/COUNT(a.transaction) AS avg_trans FROM history AS a GROUP BY a.id, a.merchant_id) derived_table
on history.merchant_id = derived_table.merchant_id

Upvotes: 0

Shadow
Shadow

Reputation: 34232

As @Strawberry indicated, you need to use a subquery to calculate the averages by id and merchant id and join this back on your main table:

select t1.*, t2.avg_trans
from table t1
inner join (select id, merchant_id, avg(transaction) avg_trans
            from table
            group by id, merchant_id) t2 on t1.id=t2.id and t1.merchant_id=t2.merchant_id

Upvotes: 3

Related Questions