通过添加值分组 [英] group by with adding the values

问题描述

我有这个查询

select ts.name as my_name, ss.step_number, p.specs, p.price, 
ssp.class_id from optional_system_step 
as ss join system as s on s.system_id=ss.system_id join category_description 
as cd on cd.category_id=ss.category_id join optional_system_step_product as 
ssp on ss.system_step_id=ssp.system_step_id join product as p on 
p.product_id=ssp.product_id join product_description as pd on 
pd.product_id=p.product_id join template_step as ts on 
(ts.template_id=s.optional_template_id and ts.step_number=ss.step_number)
where s.system_id = '15'  order by ss.step_number, ssp.class_id;

返回这个

admin   1       999.0000    1   
admin   1       1349.0000   1   
admin   1       1699.0000   1   
pay 1       479.0000    2   
pay 1       149.0000    2   
pay 1       269.0000    3   

看起来不错，但问题是我需要按 class_id 分组，但在价格字段中我需要添加三个价格，例如我会返回这两行

Seems good but the problem is that i need to group by class_id but in the price field i need to add the three prices so for example i would have these two rows returned

admin   1       4047.0000   1   
pay 1   897.0000    2

所以基本上我想将三个数字相加并在价格字段中返回该值

So basically i want to add the three numbers together and return that value in the price field

推荐答案

使用聚合函数SUM()和GROUP BY:

select ts.name as my_name, ss.step_number, p.specs, SUM(p.price),  ssp.class_id
from optional_system_step  as ss
join system as s on s.system_id=ss.system_id
join category_description  as cd on cd.category_id=ss.category_id
join optional_system_step_product as  ssp on ss.system_step_id=ssp.system_step_id
join product as p on  p.product_id=ssp.product_id
join product_description as pd on  pd.product_id=p.product_id
join template_step as ts on  (ts.template_id=s.optional_template_id and ts.step_number=ss.step_number)
where s.system_id = '15' 
GROUP BY ts.name, ss.step_number, p.spects, ssp.class_id
order by ss.step_number, ssp.class_id; 

这篇关于通过添加值分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！