group by with adding the values

I have this query

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;

which returns this

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   

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 t开发者_运维技巧hree numbers together and return that value in the price field

USE the aggregate function SUM() with 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; 

Maybe SUM or SUM with GROUP BY?

The above will actually return 3 rows if you group by class_id as you have 1,2 and 3.

I think the query you need is below but it assumes that you can group by ts.name, ss.step_number, p.specs and ssp.class_id

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.specs,
ssp.class_id
ORDER BY
    ss.step_number
,   ssp.class_id ;

The output of your query does not match the number of columns in your SELECT, so I'm not sure if there is anything that you might be missing.

But this should solve your purpose:

select ts.name as my_name, ss.step_number, p.specs, SUM(p.price) as 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 ssp.class_id;

I would also like to add that you do not need to GROUP BY on other columns because they all seem to have the same values, so GROUP BY on ssp.class_id should be fine.

Also, though not directly related to your question but I think that if you remove the category_description and product_description joins, your query should still work fine and will look a little cleaner too. I can't confirm on this since I don't understand the structure of your database.