|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SELECT Query With SumMyField1 MyField2 MyValue F1 A 5 F2 B 10 F3 A 10 F4 A 20 F5 B 5 F6 B 15 F7 A 15 F8 A 5 F9 C 15 F10 A 5 F11 A 5 F12 C 10 F13 A 5 F14 C 25 And, I need to write a query to output like the following: MyField1 MyField2 MyValue MySUM F1 A 5 70 F2 B 10 30 F3 A 10 70 F4 A 20 70 F5 B 5 30 F6 B 15 30 F7 A 15 70 F8 A 5 70 F9 C 15 50 F10 A 5 70 F11 A 5 70 F12 C 10 50 F13 A 5 70 F14 C 25 50 Please help! SELECT *,
(select sum(MyValue) from Whatever as X where A.MyField2 = X.MyField2) as MySUM FROM Whatever as A Roy Harvey Beacon Falls, CT On Thu, 8 Jun 2006 12:53:02 -0700, Saima <Sa***@discussions.microsoft.com> wrote: Show quote >I have a table like the following: > >MyField1 MyField2 MyValue >F1 A 5 >F2 B 10 >F3 A 10 >F4 A 20 >F5 B 5 >F6 B 15 >F7 A 15 >F8 A 5 >F9 C 15 >F10 A 5 >F11 A 5 >F12 C 10 >F13 A 5 >F14 C 25 > > >And, I need to write a query to output like the following: > >MyField1 MyField2 MyValue MySUM >F1 A 5 70 >F2 B 10 30 >F3 A 10 70 >F4 A 20 70 >F5 B 5 30 >F6 B 15 30 >F7 A 15 70 >F8 A 5 70 >F9 C 15 50 >F10 A 5 70 >F11 A 5 70 >F12 C 10 50 >F13 A 5 70 >F14 C 25 50 > >Please help! Saima wrote:
Show quote > I have a table like the following: I'm not sure how you calculate the value MySUM. On first impression it> > MyField1 MyField2 MyValue > F1 A 5 > F2 B 10 > F3 A 10 > F4 A 20 > F5 B 5 > F6 B 15 > F7 A 15 > F8 A 5 > F9 C 15 > F10 A 5 > F11 A 5 > F12 C 10 > F13 A 5 > F14 C 25 > > > And, I need to write a query to output like the following: > > MyField1 MyField2 MyValue MySUM > F1 A 5 70 > F2 B 10 30 > F3 A 10 70 > F4 A 20 70 > F5 B 5 30 > F6 B 15 30 > F7 A 15 70 > F8 A 5 70 > F9 C 15 50 > F10 A 5 70 > F11 A 5 70 > F12 C 10 50 > F13 A 5 70 > F14 C 25 50 > > Please help! looks like MySUM is the total of MyValue for each distinct value of MyField2. Your numbers don't add up but if that's what you intended then try the following (SQL Server 2005): SELECT MyField1, MyField2, SUM(MyValue) OVER (PARTITION BY MyField2) AS MySUM FROM tbl; Hope this helps. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx -- David,
I am getting an error "Incorrect syntax near the keyword 'OVER'" SELECT SUM(T.MyValue ) OVER (PARTITION BY T.MyValue) AS MySUM, T.MyField1,T.MyField2, T.MyValue FROM MyTable T Show quote "David Portas" wrote: > Saima wrote: > > I have a table like the following: > > > > MyField1 MyField2 MyValue > > F1 A 5 > > F2 B 10 > > F3 A 10 > > F4 A 20 > > F5 B 5 > > F6 B 15 > > F7 A 15 > > F8 A 5 > > F9 C 15 > > F10 A 5 > > F11 A 5 > > F12 C 10 > > F13 A 5 > > F14 C 25 > > > > > > And, I need to write a query to output like the following: > > > > MyField1 MyField2 MyValue MySUM > > F1 A 5 70 > > F2 B 10 30 > > F3 A 10 70 > > F4 A 20 70 > > F5 B 5 30 > > F6 B 15 30 > > F7 A 15 70 > > F8 A 5 70 > > F9 C 15 50 > > F10 A 5 70 > > F11 A 5 70 > > F12 C 10 50 > > F13 A 5 70 > > F14 C 25 50 > > > > Please help! > > I'm not sure how you calculate the value MySUM. On first impression it > looks like MySUM is the total of MyValue for each distinct value of > MyField2. Your numbers don't add up but if that's what you intended > then try the following (SQL Server 2005): > > SELECT MyField1, MyField2, > SUM(MyValue) OVER (PARTITION BY MyField2) AS MySUM > FROM tbl; > > Hope this helps. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > > I forgot to mention that I am on SQL Server 2000.
Show quote "David Portas" wrote: > Saima wrote: > > I have a table like the following: > > > > MyField1 MyField2 MyValue > > F1 A 5 > > F2 B 10 > > F3 A 10 > > F4 A 20 > > F5 B 5 > > F6 B 15 > > F7 A 15 > > F8 A 5 > > F9 C 15 > > F10 A 5 > > F11 A 5 > > F12 C 10 > > F13 A 5 > > F14 C 25 > > > > > > And, I need to write a query to output like the following: > > > > MyField1 MyField2 MyValue MySUM > > F1 A 5 70 > > F2 B 10 30 > > F3 A 10 70 > > F4 A 20 70 > > F5 B 5 30 > > F6 B 15 30 > > F7 A 15 70 > > F8 A 5 70 > > F9 C 15 50 > > F10 A 5 70 > > F11 A 5 70 > > F12 C 10 50 > > F13 A 5 70 > > F14 C 25 50 > > > > Please help! > > I'm not sure how you calculate the value MySUM. On first impression it > looks like MySUM is the total of MyValue for each distinct value of > MyField2. Your numbers don't add up but if that's what you intended > then try the following (SQL Server 2005): > > SELECT MyField1, MyField2, > SUM(MyValue) OVER (PARTITION BY MyField2) AS MySUM > FROM tbl; > > Hope this helps. > > -- > David Portas, SQL Server MVP > > Whenever possible please post enough code to reproduce your problem. > Including CREATE TABLE and INSERT statements usually helps. > State what version of SQL Server you are using and specify the content > of any error messages. > > SQL Server Books Online: > http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx > -- > > If you have 2000. then you will have to use what Roy suggested, though you
should be replacing * with the column names.  |
|||||||||||||||||||||||
Other interesting topics