我有两张桌子。用户和交易。让我们说类型 1 是学生,类型 2 是老师
用户:
+----+--------+--------------+
| id | name | user_type_id |
+----+--------+--------------+
| 1 | Noob | 1 |
| 2 | Coder | 2 |
+----+--------+--------------+
交易:
+----+---------+--------+
| id | user_id | amount |
+----+---------+--------+
| 1 | 1 | 10 |
| 2 | 2 | 10 |
| 3 | 1 | 10 |
+----+---------+--------+
我想获得每个 user_type_id 的总和和计数;像这样的东西
user_type_id:1
sum_of_transaction: 20,
number_of_transactions: 2,
user_type_id:2
sum_of_transaction: 10,
number_of_transactions: 1
我怎样才能做到这一点?菜鸟在这里
select aa.user_type_id, sum(bb.amount) as sum_of_transaction,
count(bb.id) as number_of_transactions
from users aa inner join transactions bb on aa.id = bb.user_id
group by aa.user_type_id
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句