I have a table where a specific value may be duplicated if another field exists within another group. I'm attempting to figure out how to ignore the entries duplicated.
我有一个表，如果另一个组中存在另一个字段，则可以复制特定值。我试图找出如何忽略重复的条目。
Consider this table:
考虑一下这个表：
+--------+-----+----------+ | Amount | CID | GROUP_ID | +--------+-----+----------+ | 10.0 | 1 | g1 | | 10.0 | 2 | g1 | | 5.0 | 1 | g2 | | 5.0 | 1 | g2 | +--------+-----+----------+
I'd like to return the summed amount, but only use distinct amounts with respect to CID within a group id.
我想返回总计金额，但仅在组ID中使用与CID相关的不同金额。
+------------+----------+ | net_amount | GROUP_ID | +------------+----------+ | 10.0 | g1 | | 10.0 | g2 | +------------+----------+
I've tried grouping by amount and group id and joining on group id, but to no avail.
我已经尝试按金额和组ID分组并加入组ID，但无济于事。
Much appreciated.
非常感激。
1 个解决方案
#1
1
select distinct sum(amount) as net_amount,group_id from your_table group by group_id,cid;
This can help you. Take the distinct of the output, but group it with all the columns you want.
这可以帮到你。获取输出的不同，但将其与所需的所有列组合在一起。
#1
1
select distinct sum(amount) as net_amount,group_id from your_table group by group_id,cid;
This can help you. Take the distinct of the output, but group it with all the columns you want.
这可以帮到你。获取输出的不同，但将其与所需的所有列组合在一起。