 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008sum and insert into another table?
Hi, I currently have a table which holds the following: Team Amount Date ------ --------- ----- Blue 100 01/01/08 Red 200 01/01/08 Green 500 01/01/08 Yellow 50 01/01/08 Team Amount Date ------ --------- ----- Blue 100 02/01/08 Red 200 02/01/08 Green 500 02/01/08 Yellow 50 02/01/08 I am able to show these on a graph on a daily basis, how can show sum the Amount for each team as the days go by so i can show them on a graph for a whole month. So i would have something like this: Team Amount ------ --------- Blue 200 Red 400 Green 1000 Yellow 100 Thanks Mo You can group by Year/Month and sum the amounts: SELECT CONVERT(VARCHAR(7), date, 126) AS year_month, team, SUM(amount) AS total_amount FROM Teams GROUP BY CONVERT(VARCHAR(7), date, 126), team Or if you want only the particular month data: SELECT team, SUM(amount) AS total_amount FROM Teams WHERE date >= '20080101' AND date < '20080201' GROUP BY team HTH, Plamen Ratchev http://www.SQLStudio.com Hi, I will give this ago. Thanks > You can group by Year/Month and sum the amounts: > [quoted text clipped - 18 lines] > Plamen Ratchev > http://www.SQLStudio.com Hi, This is my current ms access query. I would like the output from this query inserted into another table, how can i do this pls. SELECT Sum(dbo_salesitems.sprice) AS SumOfsprice, dbo_salesorders.srep, dbo_delv.dtaxd, dbo_delv.dntflg FROM (dbo_delv INNER JOIN dbo_delvitems ON dbo_delv.delvno = dbo_delvitems.delvnoa) INNER JOIN (dbo_salesorders INNER JOIN dbo_salesitems ON dbo_salesorders.son = dbo_salesitems.sona) ON (dbo_delvitems.dord = dbo_salesorders.son) AND (dbo_delvitems.ditem = dbo_salesitems.sonitem) GROUP BY dbo_salesorders.srep, dbo_delv.dtaxd, dbo_delv.dntflg HAVING (((dbo_salesorders.srep)="blue" Or (dbo_salesorders.srep)="green" Or (dbo_salesorders.srep)="red" Or (dbo_salesorders.srep)="yellow" Or (dbo_salesorders.srep)="isolok") AND ((dbo_delv.dtaxd)>#3/1/2008#) AND ((dbo_delv.dntflg)="Y")) ORDER BY dbo_salesorders.srep, dbo_delv.dtaxd; > You can group by Year/Month and sum the amounts: > [quoted text clipped - 18 lines] > Plamen Ratchev > http://www.SQLStudio.com Use: INSERT INTO TargetTable SELECT .... here goes your full select statement You can remove the ORDER BY clause as it makes no sense on insert. HTH, Plamen Ratchev http://www.SQLStudio.com Hi, Team Amount Date ------- --------- ------ Blue 100 01/03/2008 Blue 200 02/03/2008 Green 500 01/03/2008 Green 400 02/03/2008 How can i sum the amounts for each team. So everyday the Amount field will include the amount from the previous date. Basically i want a comulative figure for the month for each Team. Thanks for your help > Use: > [quoted text clipped - 7 lines] > Plamen Ratchev > http://www.SQLStudio.com I believe I already provided the answer to this in my first post. Here is example with all details: CREATE TABLE Teams ( team VARCHAR(10), transaction_date DATETIME, amount INT, PRIMARY KEY (team, transaction_date)) INSERT INTO Teams VALUES ('Blue', '20080301', 100) INSERT INTO Teams VALUES ('Blue', '20080302', 200) INSERT INTO Teams VALUES ('Green', '20080301', 500) INSERT INTO Teams VALUES ('Green', '20080302', 400) -- Summary amounts by team SELECT team, SUM(amount) AS total FROM Teams GROUP BY team -- Summary amounts by team for the month SELECT team, SUM(amount) AS total FROM Teams WHERE transaction_date >= '20080301' AND transaction_date < '20080401' GROUP BY team -- Summary amounts by year/month SELECT CONVERT(VARCHAR(7), transaction_date, 126) AS year_month, team, SUM(amount) AS total FROM Teams GROUP BY CONVERT(VARCHAR(7), transaction_date, 126), team -- Latest team amount SELECT team, amount FROM Teams AS T1 WHERE T1.transaction_date = (SELECT MAX(T2.transaction_date) FROM Teams AS T2 WHERE T2.team = T1.team) HTH, Plamen Ratchev http://www.SQLStudio.com Hi Plamen, I cant hardcode those dates and amounts as the amounts will be based on the amounts field. I just want to add the amount from yesterday and the day before and the day before for the current month and get a total. thanks > I believe I already provided the answer to this in my first post. Here is > example with all details: [quoted text clipped - 40 lines] > Plamen Ratchev > http://www.SQLStudio.com I am not sure where you see any hardcoding. Take this query that I posted: SELECT CONVERT(VARCHAR(7), transaction_date, 126) AS year_month, team, SUM(amount) AS total FROM Teams GROUP BY CONVERT(VARCHAR(7), transaction_date, 126), team It seems to me it does exactly what you are describing. To get a better answer it is best to post your table structure (CREATE TABLE statements), sample data, and desired results. HTH, Plamen Ratchev http://www.SQLStudio.com Hi, Sorry my fault i had a closer look at your code and yes it is what i am looking for. Thanks for your help Mo > I believe I already provided the answer to this in my first post. Here is > example with all details: [quoted text clipped - 40 lines] > Plamen Ratchev > http://www.SQLStudio.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.