Group By

[Ringers]

Hi All,

I am unable to get my group by working.

Current Results:
[Title] [A] [Name] [Count]
Conference - Depot 0 0 Camille Wood 1
Conference - Other 0 0 Camille Wood 4
Conference - Other 40 0 Camille Wood 1
Days Worksite - Depot 100 100 Camille Wood 1
Employer Inductions 0 5 Camille Wood 1
GM/Snr - Administration 0 0 Camille Wood 13
GM/Snr - Administration 0 1 Camille Wood 3
GM/Snr - Administration 0 2 Camille Wood 2
GM/Snr - Administration 0 3 Camille Wood 1
GM/Snr - Administration 0 4 Camille Wood 1
GM/Snr - Administration 0 10 Camille Wood 1
GM/Snr - Administration 2 0 Camille Wood 1
GM/Snr - Other 0 0 Camille Wood 12

Expected:
Conference - Depot 0 0 Camille Wood 1
Conference - Other 40 0 Camille Wood 1
Days Worksite - Depot 100 100 Camille Wood 1
GM/Snr - Administration 0 24 Camille Wood 9
GM/Snr - Other 0 0 Camille Wood 12

So each title is displayed once per name, column [a] and column is multiplied by the count then added.When [a] or is zero is not counted.
e.g 3x1=3,2x2=4,1x3=3,1x4=4,1x10=10
3+4+3+4+10=24

;WITH cte AS 

(SELECT e_ina23, SUBSTRING(e_ina23,7,5) AS [Div A], SUBSTRING(e_ina23,13,LEN(e_ina23)) AS [Div B],
e_icon_title as 'Activity Type', e_ina13,e_ina01, e_ina08, e_ina12, e_ina15

FROM zzz.xxxx

WHERE E_INA23 LIKE '[0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9]'

)

SELECT [Activity Type],
        
       CAST([Div A] AS INT) AS [Div A], 
       CAST([Div B] AS INT) AS [Div B], 
	   
       
       case e_ina13 
           
           when 'camillew' then 'Camille Wood'
           when 'ian' then 'Ian Peel'
           when 'michael' then 'Michael Wood'
             
       end as [Staff Member]
       ,count(*) as 'Activities During Period'


FROM cte
where  
e_ina01 = 'EISS' 
and e_ina13 in ('camillew','ian','michael') 
and e_ina08 = 'employer' 
and e_ina12 = 'activity'
  
--and e_ina15 between (@startdate) AND (@enddate)
group by [Activity Type], e_ina13, [Div A], [Div B]
[/SQL]