Update + Sum - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Update + Sum

Author

Topic

fholemans
Starting Member

12 Posts

Posted - 2012-12-03 : 08:35:07

Hi,

I have an issue concerning a SUM in an update.
Indeed, I need to copy the sum of three members of a dimension, in the same dimension but in a different member.
Hereafter the query, I have tried but without success :

Update tblFactMGMTAPP
SET M_ACCOUNT='M506'
WHERE SIGNEDDATA = (SELECT SUM(SIGNEDDATA) from tblFactMGMTAPP where M_ACCOUNT IN ('M881', 'M882','M883'))

Thanks for your help,

François

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 08:50:08

sounds like this


Update tblFactMGMTAPP
SET SIGNEDDATA = (SELECT SUM(SIGNEDDATA) from tblFactMGMTAPP where M_ACCOUNT IN ('M881', 'M882','M883'))
WHERE M_ACCOUNT='M506'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fholemans
Starting Member

12 Posts

Posted - 2012-12-03 : 08:56:28

Unfortunately it doesn't work neither.

So my goal is to sum the M_ACCOUNT 'M881' 'M882' and 'M883' and to inject this sum in one M_ACCOUNT='M506'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 09:14:06

thats exactky what above UPDATE does.
Whats the issue with above query? are you getting some error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-03 : 09:14:11

That is what exactly the code that Visakh posted does IF there is an already existing row for M_ACCOUNT = M506. If there is no row for that account, i.e., you are trying to insert a new row, use the following query. I am listing only the two columns that you described in your postings - there may be other required columns that you will have to list in the columns list and select list

INSERT INTO tblFactMGMTAPP
  (
    M_ACCOUNT,
    SIGNEDDATA
  )
SELECT 'M506',
       SUM(SIGNEDDATA)
FROM   tblFactMGMTAPP
WHERE  M_ACCOUNT IN ('M881', 'M882', 'M883')

fholemans
Starting Member

12 Posts

Posted - 2012-12-03 : 10:09:41

Vikash,

No error but any rows where affected by the update.

Sunitabeck,

Indeed, I have create this new member in my dimension M_ACCOUNT.
But I have tried your query (hereafter)

INSERT INTO tblFactMGMTAPP
(
M_ACCOUNT,
SIGNEDDATA,
GROUPS,
CATEGORY,
TIMEID,
DIRECTIONS,
PISTES
)
SELECT 'M506',
SUM(SIGNEDDATA),
'LC',
'ACTUAL',
'20120600',
'Z_STAT',
'PIMDE'
FROM tblFactMGMTAPP
WHERE M_ACCOUNT IN ('M881', 'M882', 'M883') and DIRECTIONS='Z_STAT' and PISTES='PIMDE' and CATEGORY='ACTUAL' and GROUPS='LC' and TIMEID='20120600'

but I receive this error message :

"Cannot insert the value NULL into column 'SIGNEDDATA'. Column does not allow nulls. Insert Fails"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 10:23:07

Does SIGNEDDATA field have any non NULL values for records having M_ACCOUNT values as 'M881', 'M882', 'M883'?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fholemans
Starting Member

12 Posts

Posted - 2012-12-03 : 10:51:42

Yes they are some NON NULL data on SIGNEDDATA because when I write this query :

select SUM(SIGNEDDATA)from tblFactMgmtApp where M_ACCOUNT IN ('M881','M882','M883') and PISTES='PIMDE' and GROUPS='LC' and TIMEID='20120600' and CATEGORY='ACTUAL'

My result is :

which is equal to the sum of M881, M882 and M883

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 11:01:40

whats about DIRECTIONS field value in that case?
you have an additional condition DIRECTIONS = 'Z_STAT' also in the where so worth checking if you get any sum after adding that

check below code


select SUM(SIGNEDDATA)from tblFactMgmtApp where M_ACCOUNT IN ('M881','M882','M883') and PISTES='PIMDE' and GROUPS='LC' and TIMEID='20120600' and CATEGORY='ACTUAL'
AND DIRECTIONS = 'Z_STAT'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fholemans
Starting Member

12 Posts

Posted - 2012-12-03 : 11:36:24

Thanks a lot !
Now it works.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 11:41:07

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fholemans
Starting Member

12 Posts

Posted - 2012-12-04 : 03:49:03

I have a last question :

So this query works well :

INSERT INTO tblFactMGMTAPP
(M_ACCOUNT,SIGNEDDATA,GROUPS,CATEGORY,TIMEID,DIRECTIONS,PISTES)
SELECT 'M506',SUM(SIGNEDDATA),'LC','ACTUAL','20120600','Z_STAT','PIMDE'
FROMtblFactMGMTAPP
WHERE M_ACCOUNT IN ('M881', 'M882', 'M883') and DIRECTIONS='Z_STAT' and PISTES='PIMDE' and CATEGORY='ACTUAL' and GROUPS='LC' and TIMEID='20120600'

but I have to work on different times, different PISTES and different CATEGORY so I want to know if there is a solution with the query above to go faster. Because now I have to change each time the dimension.

Thanks in advance

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-04 : 05:17:58

Case1: For all different values of Directions, timeid, pistes

INSERT INTO tblFactMGMTAPP
(M_ACCOUNT,SIGNEDDATA,GROUPS,CATEGORY,TIMEID,DIRECTIONS,PISTES)
SELECT 'M506',SUM(SIGNEDDATA),'LC','ACTUAL',Directions, timeid, pistes
FROMtblFactMGMTAPP
WHERE M_ACCOUNT IN ('M881', 'M882', 'M883') and CATEGORY='ACTUAL' and GROUPS='LC'
GROUP BY Directions, timeid, pistes

Case2: For specific Directions, timeid, pistes values

DECLARE @timeid date = '20120600'
DECLARE @PISTES varchar(20) = 'PIMDE'
DECLARE @dirctn varchar(10) = 'Z_STAT'

INSERT INTO tblFactMGMTAPP
(M_ACCOUNT,SIGNEDDATA,GROUPS,CATEGORY,TIMEID,DIRECTIONS,PISTES)
SELECT 'M506',SUM(SIGNEDDATA),'LC','ACTUAL',@timeid,@dirctn,@dirctn
FROMtblFactMGMTAPP
WHERE M_ACCOUNT IN ('M881', 'M882', 'M883') and DIRECTIONS=@dirctn and PISTES=@PIMDE and CATEGORY='ACTUAL' and GROUPS='LC' and TIMEID=@timeid

--
Chandu

fholemans
Starting Member

12 Posts

Posted - 2012-12-04 : 06:31:04

Thanks a lot for all you answers.
It works perfect now ;-)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-04 : 07:27:10