codice:
select codice_dip,cognome,sum(valore) as totale
from dbo.tabella
where codice_val in ('a','c')
group by codice_dip,cognome
order by codice_dip