SELECT e.DEPT, e.Employee, t.CurrSum 
FROM EE e
INNER JOIN (SELECT  DEPT,SUM(Curr_$) AS CurrSum
            FROM EE
            GROUP BY DEPT)t
ON t.DEPT=e.DEPT
ORDER BY e.DEPT, e.Employee

quote:

---

Originally posted by parrot

The method you suggested didn't work. It gave me redundant records with very large values for a sum. How can Enteprise Manager display all of the results and I can't using C# in my Visual Studio program. It does sound like a limitation in C# programming. I really need to show subtotals within detail information like below:

DEPT Employee CurrSum

01 11111 10.00
01 22222 30.00
01 33333 20.00
Subtotal 60.00

02 12345 5.00
02 34567 15.00
Subtotal 20.00

Anyone have a clue as how to create an SQL command to create the above results?
Dave

---

quote:

---

Originally posted by parrot

The method you are referring to doesn't apply to my situation because I am dynamically creating reports in which I do not know what the report columns will be. Therefore, I cannot use the second method of computing totals referenced in the article. The first method does not apply either since I am using the COMPUTE statement instead of the GROUP BY statement to generate my totals and the SQL statements when executed only return the first control without any totals. I need to understand why all of the data is not being returned when I execute the fill command. It still seems like a bug or limitation with the way C# processes the SQL command. If I remove the COMPUTE statement then all the records are returned. If I include the COMPUTE statement only the records for the first control (DEPT) are returned without any total.
Dave

---

quote:

---

Originally posted by parrot

I do want a total for all paytypes for each employee. There are multiple paytypes for each employee. In my example there is a total of 30.00 for 2 different paytypes. The problem remains that any field in the select statement that precedes the field being summarized must also be included in the order by and group by statements.

---

SELECT t.DEPT, t.EMPLOYEE, t.NAME, t.PAYTYPE,tmp.Total
FROM EE t
INNER JOIN (SELECT DEPT, EMPLOYEE, NAME,SUM(CURR_$) AS Total
            FROM EE
            GROUP BY DEPT, EMPLOYEE, NAME)tmp
ON tmp.DEPT =t.DEPT 
AND tmp.EMPLOYEE=t.EMPLOYEE
AND tmp.NAME=t.NAME
ORDER BY t.DEPT, t.EMPLOYEE, t.NAME, t.PAYTYPE