> Hello Everyone
>
> How can I update this table to show an aggregate(sum) on a column?
>
> CREATE TABLE [SUR2] (
>     [Domain] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL,
>     [WorkPack] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
>     [InvoiceMonth] [datetime] NULL ,
>     [InvoiceTotal] [float] NULL ,
>    
> ) ON [PRIMARY]
> GO
>
>
> INSERT INTO [SUR2]([Domain], [WorkPack], [InvoiceMonth], [InvoiceTotal])
> VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> VALUES(SUR2, YYY-YYY, 3/31/2006, 100)
> VALUES(SUR2, ZZZ-ZZZ, 2/31/2006, 100)
>
> Now I want to update the values in SUR2 to reflect
>
> Domain    WorkPack    InvoiceMonth    InvoiceTotals
> SUR2    XXX-XXX        1/31/2006    200
> SUR2    YYY-YYY        3/31/2006    100
> SUR2    ZZZ-ZZZ        2/31/2006    100
>
> Is there a way to update the table and Aggregate the Sum of the Column
> [InvoiceTotal]   
>
>
> Thanks In Advance
> kw   

"Tracy McKibben" wrote:

> kw_uh97 wrote:
> > Hello Everyone
> >
> > How can I update this table to show an aggregate(sum) on a column?
> >
> > CREATE TABLE [SUR2] (
> >     [Domain] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL,
> >     [WorkPack] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> >     [InvoiceMonth] [datetime] NULL ,
> >     [InvoiceTotal] [float] NULL ,
> >    
> > ) ON [PRIMARY]
> > GO
> >
> >
> > INSERT INTO [SUR2]([Domain], [WorkPack], [InvoiceMonth], [InvoiceTotal])
> > VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> > VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> > VALUES(SUR2, YYY-YYY, 3/31/2006, 100)
> > VALUES(SUR2, ZZZ-ZZZ, 2/31/2006, 100)
> >
> > Now I want to update the values in SUR2 to reflect
> >
> > Domain    WorkPack    InvoiceMonth    InvoiceTotals
> > SUR2    XXX-XXX        1/31/2006    200
> > SUR2    YYY-YYY        3/31/2006    100
> > SUR2    ZZZ-ZZZ        2/31/2006    100
> >
> > Is there a way to update the table and Aggregate the Sum of the Column
> > [InvoiceTotal]   
> >
> >
> > Thanks In Advance
> > kw   
>
> Unless you're building a data warehouse, you shouldn't store aggregate
> values like this.  If you're going to store the aggregate, then you need
> some method of guaranteeing that it stays updated.  Much safer to just
> calculate on the fly as needed.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

"kw_uh97" <kwu***@discussions.microsoft.com> wrote in message
news:666C7813-B68D-4418-AD71-2D189ADF3329@microsoft.com...
> Hello Everyone
>
> How can I update this table to show an aggregate(sum) on a column?
>
> CREATE TABLE [SUR2] (
> [Domain] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL,
> [WorkPack] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> [InvoiceMonth] [datetime] NULL ,
> [InvoiceTotal] [float] NULL ,
>
> ) ON [PRIMARY]
> GO
>
>
> INSERT INTO [SUR2]([Domain], [WorkPack], [InvoiceMonth], [InvoiceTotal])
> VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> VALUES(SUR2, YYY-YYY, 3/31/2006, 100)
> VALUES(SUR2, ZZZ-ZZZ, 2/31/2006, 100)
>
> Now I want to update the values in SUR2 to reflect
>
> Domain WorkPack InvoiceMonth InvoiceTotals
> SUR2 XXX-XXX 1/31/2006 200
> SUR2 YYY-YYY 3/31/2006 100
> SUR2 ZZZ-ZZZ 2/31/2006 100
>
> Is there a way to update the table and Aggregate the Sum of the Column
> [InvoiceTotal]
>
>
> Thanks In Advance
> kw

"Arnie Rowland" wrote:

> Do you want to have two rows (SUR2, XXX-XXX, 1/31/2006, 200), or only one
> row.
>
> If only one row, then use a GROUP BY, summing the [InvoiceTotal].
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "kw_uh97" <kwu***@discussions.microsoft.com> wrote in message
> news:666C7813-B68D-4418-AD71-2D189ADF3329@microsoft.com...
> > Hello Everyone
> >
> > How can I update this table to show an aggregate(sum) on a column?
> >
> > CREATE TABLE [SUR2] (
> > [Domain] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL,
> > [WorkPack] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> > [InvoiceMonth] [datetime] NULL ,
> > [InvoiceTotal] [float] NULL ,
> >
> > ) ON [PRIMARY]
> > GO
> >
> >
> > INSERT INTO [SUR2]([Domain], [WorkPack], [InvoiceMonth], [InvoiceTotal])
> > VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> > VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> > VALUES(SUR2, YYY-YYY, 3/31/2006, 100)
> > VALUES(SUR2, ZZZ-ZZZ, 2/31/2006, 100)
> >
> > Now I want to update the values in SUR2 to reflect
> >
> > Domain WorkPack InvoiceMonth InvoiceTotals
> > SUR2 XXX-XXX 1/31/2006 200
> > SUR2 YYY-YYY 3/31/2006 100
> > SUR2 ZZZ-ZZZ 2/31/2006 100
> >
> > Is there a way to update the table and Aggregate the Sum of the Column
> > [InvoiceTotal]
> >
> >
> > Thanks In Advance
> > kw
>
>
>

"kw_uh97" wrote:

> Thanks for the response Arniw
>
> One row is what I am looking for here. I am familar with the GROUP BY
> clause. Where I was in need of help is the UPDATE statement. How will I
> incorporate the set to value and the GROUP BY clause? Hope I am making sense.
>
> kw
>
> "Arnie Rowland" wrote:
>
> > Do you want to have two rows (SUR2, XXX-XXX, 1/31/2006, 200), or only one
> > row.
> >
> > If only one row, then use a GROUP BY, summing the [InvoiceTotal].
> >
> > --
> > Arnie Rowland, Ph.D.
> > Westwood Consulting, Inc
> >
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > "kw_uh97" <kwu***@discussions.microsoft.com> wrote in message
> > news:666C7813-B68D-4418-AD71-2D189ADF3329@microsoft.com...
> > > Hello Everyone
> > >
> > > How can I update this table to show an aggregate(sum) on a column?
> > >
> > > CREATE TABLE [SUR2] (
> > > [Domain] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL,
> > > [WorkPack] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
> > > [InvoiceMonth] [datetime] NULL ,
> > > [InvoiceTotal] [float] NULL ,
> > >
> > > ) ON [PRIMARY]
> > > GO
> > >
> > >
> > > INSERT INTO [SUR2]([Domain], [WorkPack], [InvoiceMonth], [InvoiceTotal])
> > > VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> > > VALUES(SUR2, XXX-XXX, 1/31/2006, 100)
> > > VALUES(SUR2, YYY-YYY, 3/31/2006, 100)
> > > VALUES(SUR2, ZZZ-ZZZ, 2/31/2006, 100)
> > >
> > > Now I want to update the values in SUR2 to reflect
> > >
> > > Domain WorkPack InvoiceMonth InvoiceTotals
> > > SUR2 XXX-XXX 1/31/2006 200
> > > SUR2 YYY-YYY 3/31/2006 100
> > > SUR2 ZZZ-ZZZ 2/31/2006 100
> > >
> > > Is there a way to update the table and Aggregate the Sum of the Column
> > > [InvoiceTotal]
> > >
> > >
> > > Thanks In Advance
> > > kw
> >
> >
> >

"rpresser" wrote:

>
> kw_uh97 wrote:
> > This is what I've tried already.
> >
> > UPDATE SUR2
> > SET InvoiceTotal = (select workpack, InvoiceMonth, SUM(InvoiceTotal) As
> > 'Invoice Total'
> > from SUR2
> > group by workpack, invoicemonth)
> >
> >
> > and gotten this error message: Only one expression can be specified in the
> > select list when the subquery is not introduced with EXISTS.
>
>
> You have said that your ultimate desire is to have only one row per
> group instead of two or more. Therefore an UPDATE, which can only
> change the value of columns in a row and cannot delete rows, cannot be
> used to do what you want.
>
> SELECT domain,workpack, InvoiceMonth, SUM(Invoicetotal) AS
> 'InvoiceTotal'
> INTO #TempSUR2
> FROM SUR2
> GROUP BY domain,workpack, invoicemonth
>
> DELETE FROM SUR2
>
> INSERT INTO SUR2 (domain,workpack,InvoiceMonth,InvoiceTotal)
> SELECT domain,workpack,InvoiceMonth,InvoiceTotal
> FROM #TempSUR2
>
> DROP #TempSUR2
>
>