Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Updating new column using a subquery


I have a table Regions with the RegionID and RegionState. I am trying to add
a new column so that when I run my query I get a total sales for that region.
I understand I have to use a correlated query but I am not making the
connection between the inner query to the outer query in order to pass the
TotalSales.

ALTER TABLE Regions
        ADD TotalSales money  not null default 0

BEGIN Transaction

UPDATE Regions
SET TotalSales=(SELECT Sum (Qty*Price)FROM OrderItems
WHERE R.RegionID=RS.RegionID
 SELECT R.RegionID,R.RegionName
        FROM Customers C
        INNER JOIN Orders O
        ON C.CustomerID=O.CustomerID
        INNER JOIN OrderItems OI
        ON O.InvoiceID=OI.InvoiceID
        INNER JOIN RegionStates RS
        ON C.State=RS.State
        INNER JOIN Regions R
        ON RS.RegionID=R.RegionID
Group by R.RegionID,R.RegionName

ROLLBACK Transaction

UPDATE r
SET TotalSales = sub.TotalSales
FROM Regions r
INNER JOIN
(
    SELECT RegionID,
        TotalSales = SUM(Qty*Price)
        FROM OrderItems
        GROUP BY RegionID
) sub
ON r.RegionID = sub.RegionID

I'm not sure why you brought in all that extra stuff, from customers,
regionstates, etc.

I'm also not sure why you want to store this data when it will be much
better to calculate it at runtime.  The information is redundant and if any
row changes you will need to re-calculate.

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

"RuthE" <R@discussions.microsoft.com> wrote in message

news:2415B464-09E9-4B8F-95B2-10197794B95F@microsoft.com...

Aarton
Thanks for your quick response. I am new to SQL which is why I was trying to
do all of the joins. I'm having a hard time understanding how a value is
returned back to the main query. Can you explain or refer me to a link that
might explain further? In my case I did the join because otherwise there is
nothing to link the Regions table to the OrderItems table.

"Aaron Bertrand [SQL Server MVP]" wrote:

You're just joining against the subquery.  You should see by running it on
its own, there is exactly one row per region.  This is what you are joining
against when you run the update.

     SELECT RegionID,
         TotalSales = SUM(Qty*Price)
         FROM OrderItems
         GROUP BY RegionID

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

"RuthE" <R@discussions.microsoft.com> wrote in message

news:6468F439-C77B-49AC-ACA7-6CAF68A3C11D@microsoft.com...

>> I have a table Regions with the region_id and region_sate. I am trying to add a new column so that when I run my query I get a total sales for that region.<<

Why are you adding a computed column to a table ??!!

Why did you use MONEY -- a non-ANSI data type with mathematical
problems, of the love of Ghod!!

Real SQL programmers do it with a VIEW that is always correct. Write a
VIEW!!

CREATE VIEW RegionWithTotals
AS
SELECT <<no idea because of crappy specs >>,
  FROM Regions
 WHERE .. ;

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.  Just basic netiquette, please.  We are doing your job for you
and you should show us basic Netiquette.

Oh, you need to read and learn ISO-11179 naming rules and how to
format SQL.  You have just been told by someone who helped write that
your code and approach stink.  Learn, before you hurt or kill
someone.

-----------------------------------------------Reply-----------------------------------------------

"--CELKO--" <jcelko@earthlink.net> wrote in message

news:1181068569.848068.290610@w5g2000hsg.googlegroups.com..

> Real SQL programmers do it with a VIEW that is always correct. Write a
> VIEW!!

Real SQL programmers should stick to their sandbox and recuse themselves
from application development. You flash ignorance like a streaker.

> Oh, you need to read and learn ISO-11179 naming rules and how to
> format SQL.  You have just been told by someone who helped write that
> your code and approach stink.

You want credit for something a mile wide and an inch deep.
And one still needs boots.

www.beyondsql.blogspot.com

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc