Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update table

Posted on 2009-04-21
7
Medium Priority
?
159 Views
Last Modified: 2012-05-06
Hi,

I have this table with these field.
ProjectID    CrAmount     DbAmount      Diff
1987              5                    2    
1987              4                    7  
6931              6                    2
1987              9                    3
6931              5                    4
4511              9                    2  

what I want is to make the data like this table
order by projectId
sum CrAmount      Sum DrAmount
and put the difference in the difference column.

this is the output that I want.
ProjectID    CrAmount     DbAmount      Diff
1987             18                  12               6
6931              3                    6                -3
4511              9                    2                 7


         
0
Comment
Question by:lulu50
  • 4
  • 3
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 24198232
this should do:
select ProjectId, sum(crAmount), sum(ddamount), sum(diff)
  from yourtable
 group by projetid

Open in new window

0
 

Author Comment

by:lulu50
ID: 24198269
I have to do an update to the temp table
0
 

Author Comment

by:lulu50
ID: 24198283
the diff is the sum(cramount) - sum(ddamount) as diff
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24198327
sorry:
select ProjectId, sum(crAmount), sum(ddamount), sum(crAmount) - sum(ddamount)
  from yourtable
 group by projetid

Open in new window

0
 

Author Comment

by:lulu50
ID: 24198602
How can I clear data from a #tempTable
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24198640
truncate table #tempTable

or

drop and recreate it
0
 

Author Closing Comment

by:lulu50
ID: 31572975
Thanks
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question