# Thread: SUM Multiply based on column entry

1. Registered User
Join Date
Aug 2012
Posts
8

## Unanswered: SUM Multiply based on column entry

SELECT EmployeeId, PiecemealType,SUM(PiecemealQty) /100 AS TotalTrays, ActivityId
FROM SR.dbo.PayTrays
WHERE WorkDate > '2012-01-01'
AND WorkDate < '2012-12-31'
GROUP BY EmployeeId, PiecemealType, ActivityId
ORDER BY EmployeeId, PiecemealType

PiecemealType is the type of tray
1= 15 Items per tray
2= 12 Items per tray
3= 8 Items per tray
4= 6 Items per tray

I'm trying to work out a bonus for 1 cent per item worked. The divide by 100 moves the total from cent to dollar and the SUM is adding all weekly values together.

I just need to work out how to multiply the the SUM value for trays by the 4 different PiecemealType values. If it was static i could just add *15 to my SUM.

2. Registered User
Join Date
Apr 2012
Posts
213
I do not know if I understand your question correctly, but try:

Code:
```SUM(PiecemealQty) / 100 *
CASE PiecemealType
WHEN 1 THEN 15
WHEN 2 THEN 12
WHEN 3 THEN 8
ELSE 6
END AS TotalTrays```
Hope this helps.

3. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,101