如何选择某些"id"的总和并按组划分结果 [英] How to select sums of certain 'id' and devide the result by groups
问题描述
在此查询中,我尝试选择用户已付款的金额:
In this query I try to select the sums of payments the users had made:
SELECT *
from (select IFnull(t.diapason,'total') as diapason,
t.total_amount as total_amount
FROM
(SELECT p.user_id, p.amount as total_amount, CASE
when amount<=100 then '0-100'
when amount>100 then '100...' END AS diapason
FROM (SELECT distinct payments.user_id, SUM(amount) AS amount
FROM payments inner JOIN (SELECT DISTINCT user_id FROM payments where
payment_time between '2000-01-01' and '2001-01-01') a ON
payments.user_id = a.user_id
GROUP BY payments.user_id) p) t
GROUP BY diapason WITH ROLLUP) as t1
ORDER BY total_amount desc;
但是我得到的结果是错误的.我应该更改什么才能找出2000年1月1日至2001年1月1日之间的工资总额.由于用户曾经付款,结果必须按组划分.
But result I've got is wrong. What should I change to find out sum of paymens for period 2000-01-01 - 2001-01-01. Result must be devided by group due to payments user ever did.
这些是表格的活动和付款.
These are tables activity and payments.
activity
user_id login_time
1 2000-01-01
2 2000-03-01
....
payments
user_id payment_time amount
1 2000-05-04 10
1 2000-03-01 20
2 2000-04-05 5
...
通常,组由每个用户曾经支付的总金额组成.例如,如果他支付了50美元-他在"0-100"组中.如果他支付了500,那么他就属于"100 ..."组,但是现在我需要知道每个组中用户的付款总额.
Generally, groups formed by total amount each user ever payed. For example if he payed 50 $ - he is in group "0-100". If he payed 500 - he is in the group "100..." But now I need to know sum of payments users made in each group.
谢谢您的帮助!
推荐答案
如果我正确理解,则不需要所有这些子查询.
简单的sum
和group by
应该做到这一点:
If I understood correctly, no need for all those sub-queries.
Simple sum
and group by
should do it:
SELECT t1.user_id, sum(t2.amount) as total, IF(sum(t2.amount)<=100, '0-100', '100...') as grp
FROM t1
INNER JOIN t2 on t1.user_id = t2.user_id
WHERE t2.payment_time between '2000-01-01' and '2001-01-01'
GROUP BY t1.user_id
这篇关于如何选择某些"id"的总和并按组划分结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!