Need a Query to sumup the values according to Depa

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need a Query to sumup the values according to Depa

Author

Topic

mohanaraj
Starting Member

3 Posts

Posted - 2010-09-16 : 23:40:52

Hai,

I want to sum up the child department values and display in the parent record

Below is the Table.

DepartmentId ParentId DeptName Incoming
1 ------------0------ PhnDeprt ------14.00
3 ------------1-------Facilities ------01.00
2 ------------0-------Calse --------- 5.00
4 ------------2-------CalFacitity ----1.00
5 ----------- 4 -------CalFF ---------2.00

I want the result as

DepartmentId ParentId DeptName Incoming
1 ------------0------ PhnDeprt ------15.00
3 ------------1-------Facilities ------01.00
2 ------------0-------Calse --------- 8.00
4 ------------2-------CalFacitity ----3.00
5 ----------- 4 -------CalFF ---------2.00

Here 'PhnDeprt' department have child 'Facilities' so in the result 1.00 + 14.00 = 15.00 this result will be displayed in the Parent record i.e. in the 'PhnDeprt''s Incoming column

I tried WITH but how can I use Aggregate function in WITH for sumup ?

Please advise and help me

Thanks in advance.

Best Regards,
Mohanaraj.

Sachin.Nand

2937 Posts

Posted - 2010-09-17 : 03:03:06

[code]
DECLARE @tbl AS TABLE(
departmentid INT,
parentid INT,
deptname VARCHAR(20),
incoming INT
)

INSERT INTO @tbl
SELECT 1 ,0, 'PhnDeprt' ,14.00 UNION ALL
SELECT 3 ,1,'Facilities' ,01.00 UNION ALL
SELECT 2 ,0,'Calse' , 5.00 UNION ALL
SELECT 4 ,2,'CalFacitity' ,1.00 UNION ALL
SELECT 5 ,4 ,'CalFF',2.00

SELECT *
FROM @tbl;

WITH cte
AS (SELECT departmentid,
parentid,
deptname AS deptname,
Convert(VARCHAR(20),NULL) childname,
incoming,
0 inc
FROM @tbl
UNION ALL
SELECT t.departmentid,
t.parentid,
c.deptname,
t.deptname,
c.incoming,
t.incoming
FROM @tbl t
INNER JOIN cte c
ON c.parentid = t.departmentid)
SELECT t1.departmentid,
parentid,
deptname,
Coalesce(incoming + SUM,incoming) Incoming
FROM (SELECT Sum(incoming) AS SUM,
departmentid
FROM cte c
WHERE childname IS NOT NULL
GROUP BY departmentid) t
RIGHT JOIN @tbl t1
ON t1.departmentid = t.departmentid
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mohanaraj
Starting Member

3 Posts

Posted - 2010-09-17 : 03:48:05

Hai

Thanks very much..

How to add one more column as Incoming ??

I use "Local" like below

WITH cte
AS (SELECT departmentid,parentid,deptname AS deptname, Convert(VARCHAR(20),NULL) childname,Local, incoming,0 inc FROM @tbl
UNION ALL SELECT t.departmentid, t.parentid, c.deptname, t.deptname,c.Local,t.Local, c.incoming, t.incoming FROM @tbl t
INNER JOIN cte c ON c.parentid = t.departmentid
)
SELECT t1.departmentid, parentid, deptname, Coalesce(Local + LSUM,Local) Local ,Coalesce(incoming + SUM,incoming) Incoming
FROM (SELECT Sum(Local) AS LSUM,Sum(incoming) AS SUM, departmentid FROM cte c WHERE childname IS NOT NULL GROUP BY departmentid
) t RIGHT JOIN @tbl t1 ON t1.departmentid = t.departmentid

But error will be displayed as
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Best Regards,
Mohanaraj.

Sachin.Nand

2937 Posts

Posted - 2010-09-17 : 04:43:50

Can you add the column & the data in the following table below?


DECLARE  @tbl  AS  TABLE( 
                         departmentid INT, 
                         parentid     INT, 
                         deptname     VARCHAR(20), 
                         incoming     INT 
                         ) 

INSERT INTO @tbl 
SELECT 1 ,0, 'PhnDeprt' ,14.00 UNION ALL
SELECT 3 ,1,'Facilities' ,01.00 UNION ALL
SELECT 2 ,0,'Calse' , 5.00 UNION ALL
SELECT 4 ,2,'CalFacitity' ,1.00 UNION ALL
SELECT 5 ,4 ,'CalFF',2.00

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

mohanaraj
Starting Member

3 Posts

Posted - 2010-09-17 : 05:00:38

Hai,

Yes I add the column in the table please check my below code...

DECLARE @tbl AS TABLE(
departmentid INT,
parentid INT,
deptname VARCHAR(20),
incoming INT,
Local INT
)

INSERT INTO @tbl
SELECT 1 ,0, 'PhnDeprt' ,14.00,15.00 UNION ALL
SELECT 3 ,1,'Facilities' ,01.00,13.00 UNION ALL
SELECT 2 ,0,'Calse' , 5.00,1.00 UNION ALL
SELECT 4 ,2,'CalFacitity' ,1.00,2.00 UNION ALL
SELECT 5 ,4 ,'CalFF',2.00,4.00

SELECT * FROM @tbl;

WITH cte
AS (SELECT departmentid,
parentid,
deptname AS deptname,
Convert(VARCHAR(20),NULL) childname,
incoming,
Local,
0 inc
FROM @tbl
UNION ALL
SELECT t.departmentid,
t.parentid,
c.deptname,
t.deptname,
c.incoming,
t.incoming,
c.Local,
t.Local
FROM @tbl t
INNER JOIN cte c
ON c.parentid = t.departmentid)
SELECT t1.departmentid,
parentid,
deptname,
Coalesce(incoming + SUM,incoming) Incoming ,
Coalesce(Local + LSUM,Local) Local
FROM (SELECT Sum(incoming) AS SUM,
Sum(Local) AS LSUM,
departmentid
FROM cte c
WHERE childname IS NOT NULL
GROUP BY departmentid) t
RIGHT JOIN @tbl t1
ON t1.departmentid = t.departmentid

Best Regards,
Mohanaraj.

Sachin.Nand

2937 Posts

Posted - 2010-09-17 : 05:04:34

[code]
DECLARE @tbl AS TABLE(
departmentid INT,
parentid INT,
deptname VARCHAR(20),
incoming INT,
Local INT
)

INSERT INTO @tbl
SELECT 1 ,0, 'PhnDeprt' ,14.00,15.00 UNION ALL
SELECT 3 ,1,'Facilities' ,01.00,13.00 UNION ALL
SELECT 2 ,0,'Calse' , 5.00,1.00 UNION ALL
SELECT 4 ,2,'CalFacitity' ,1.00,2.00 UNION ALL
SELECT 5 ,4 ,'CalFF',2.00,4.00

SELECT * FROM @tbl;

WITH cte
AS (SELECT departmentid,
parentid,
deptname AS deptname,
Convert(VARCHAR(20),NULL) childname,
incoming,
0 inc,
LOCAL,
0 lcl
FROM @tbl
UNION ALL
SELECT t.departmentid,
t.parentid,
c.deptname,
t.deptname,
c.incoming,
t.incoming,
c.LOCAL,
t.LOCAL
FROM @tbl t
INNER JOIN cte c
ON c.parentid = t.departmentid)
SELECT t1.departmentid,
parentid,
deptname,
Coalesce(incoming + SUM,incoming) incoming,
Coalesce(LOCAL + lsum,LOCAL) LOCAL
FROM (SELECT Sum(incoming) AS SUM,
Sum(LOCAL) AS lsum,
departmentid
FROM cte c
WHERE childname IS NOT NULL
GROUP BY departmentid) t
RIGHT JOIN @tbl t1
ON t1.departmentid = t.departmentid

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources