How to get Sum of Percentages by Employee and Line Nbr-CodePudding

I have a data that set that gives me an employee and his/her percentage. An employee can have, say, five lines or four lines of percentage data. How do I aggregate based on the line number 1 thru 5 and then again from 1 to 4 etc.

My code is aggregating total for all line numbers 1 or 2 but not 1 thru 5.

Employee	Line#	Pct
1234	1	10
1234	2	40
1234	3	50
1235	1	50
1235	2	30

I want the aggregate of 1234 to be 100 and 12345 to be 80.

SELECT EMPLOYEE, LINE_NBR,SUM(PLAN_PCT)
 FROM dbo.EmpPct
WHERE EMPLOYEE= 1234
GROUP BY LINE_NBR, EMPLOYEE
ORDER BY SUM(PLAN_PCT) DESC

CodePudding user response：

try it

SELECT EMPLOYEE,SUM(PLAN_PCT)
 FROM dbo.EmpPct
WHERE EMPLOYEE= 1234
GROUP BY EMPLOYEE
ORDER BY SUM(PLAN_PCT) DESC

CodePudding user response：

Since you want 1234 to be 100 and 1235 to be 80, there is no use for Line#. Therefore, you have to exclude Line# from your SELECT statement. That's plain logic.

SELECT   EMPLOYEE, SUM(PLAN_PCT)
FROM     dbo.EmpPct
GROUP BY EMPLOYEE
ORDER BY SUM(PLAN_PCT) DESC