How to add column and then sum
-
I have this same statement, can't seem to figure this out. How can I SUM the 'current amount' column by Month? I would like add an additional column and then populate it with the summed value of ' current amount' by month. So for example - January would have a summed total, Feb, etc.
select * from (SELECT
distinct pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2)
when '01' then 'January' when '02' then 'February' when '03' then 'March' when '04' then 'April'
when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September'
when '10' then 'October' when '11' then 'November' when '12' then 'December' end as [UltiMonth],
rtrim(eepNameLast) +
', ' + rtrim(eepNameFirst) +
' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,
eepNameLast AS [Last Name],
IsNull(eepNameSuffix,'') AS [Suffix],
eepNameFirst AS [First Name],
IsNull(eepNameMiddle,'') AS [Middle Name],
pehCurAmt AS [Current Amount],
pehCurHrs AS [Current Hours],
pehCoID AS [Company ID],
pehEEID AS [EE ID],
pehEmpNo AS [Emp No],
pehLocation AS [Location],
pehJobCode AS [Job Code],
pehOrgLvl1 AS [Org Level 1],
pehOrgLvl2 AS [Org Level 2],
pehOrgLvl3 AS [Org Level 3],
pehOrgLvl4 AS [Org Level 4],
pehPayGroup AS [Pay Group],
pehProject AS [Project],
pehShfShiftAmt AS [Shift Amount],
pehearncode AS [Earn Code],
pehIsVoided AS [IS Voided],
pehIsVoidingRecord AS [Voiding Record],
pehIsOvertime AS [Is Overtime]
FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID
join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2) t
right outer join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]
where pehPerControl > '201301011'
AND [EARN CODE] = '0002'
AND [IS Voided] <> 'Y'
AND [Voiding Record] <> 'Y'
AND [Is Overtime] = 'Y'
-
solution in case this helps someone else:
;WITH cte AS
(
select * from (SELECT
distinct pehPErcontrol
,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2)
when '01' then 'January'
when '02' then 'February'
when '03' then 'March'
when '04' then 'April'
when '05' then 'May'
when '06' then 'June'
when '07' then 'July'
when '08' then 'August'
when '09' then 'September'
when '10' then 'October'
when '11' then 'November'
when '12' then 'December'
end as [UltiMonth],
rtrim(eepNameLast) +
', ' + rtrim(eepNameFirst) +
' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,
eepNameLast AS [Last Name],
IsNull(eepNameSuffix,'') AS [Suffix],
eepNameFirst AS [First Name],
IsNull(eepNameMiddle,'') AS [Middle Name],
pehCurAmt AS [Current Amount],
pehCurHrs AS [Current Hours],
pehCoID AS [Company ID],
pehEEID AS [EE ID],
pehEmpNo AS [Emp No],
pehLocation AS [Location],
pehJobCode AS [Job Code],
pehOrgLvl1 AS [Org Level 1],
pehOrgLvl2 AS [Org Level 2],
pehOrgLvl3 AS [Org Level 3],
pehOrgLvl4 AS [Org Level 4],
pehPayGroup AS [Pay Group],
pehProject AS [Project],
pehShfShiftAmt AS [Shift Amount],
pehearncode AS [Earn Code],
pehIsVoided AS [IS Voided],
pehIsVoidingRecord AS [Voiding Record],
pehIsOvertime AS [Is Overtime]
FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID
join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013]
ON [orglevel] = pehOrgLvl2) t
right outer join
WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob
on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]
where pehPerControl > '201301011'
AND [EARN CODE] = '0002'
AND [IS Voided] <> 'Y'
AND [Voiding Record] <> 'Y'
AND [Is Overtime] = 'Y'
AND [org level 2] like '%ZSW'
--AND [ULTIMONTH] = 'MARCH'
--ORDER BY pehPerControl
)
SELECT *
,SUM([Current Amount])
OVER (PARTITION BY [UltiMonth]) AS MonthlyAmount
FROM cte
-
You don't even need the CTE, you could just add the SUM() OVER() to your original query.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply