Using a sum finction in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using a sum finction in SQL

Author

Topic

PipTo
Starting Member

1 Post

Posted - 2012-07-19 : 07:41:17

Hi,

I am hoping someone can point me in the correct direction. I have the below code that generates the correct data. However I need to group my results by Project Number and display a total sum for each one. See below examples:

Current Code:

select
PAprojname 'Project Name',
PAPROJNUMBER 'Project Number',
case PASTAT
when 1 then 'Open'
else 'Closed'
end 'Status',
case PAProjectType
when 3 then 'Fixed Price'
else 'Time and Materials'
end 'Project Type',
PAProject_Fee_Amount 'Project Fee Amount',
PAFTotalCost 'Forecast Total Cost',
PAPostedTotalCostN 'Actual Total Cost',
PAFQuantity 'Forecast Quantity',
PAFProfit 'Forcast Profit',
PABusMgrID 'Project Manager ID',
PAProject_Amount 'Project Amount',
PAUnposted_Project_Fee 'Unpost Project Fee Amount',
PAPosted_Tax_Amount 'Actual Tax Amount',
PAPostRecogRevN 'Actual Recognized Revenue',
PAPosted_Project_Fee 'Actual Project Fee Amount',
PAWrite_UpDown_Amount 'Write Up/Down Amount',
PABilled_Accrued_Revenu 'Billed Accrued Revenue',
PA_Earnings 'Earnings',
PA_Cost_of_Earnings 'Cost of Earnings',
PAUnpostBillN 'Unposted Billings',
PAUnposted_Sales_Tax_Am 'Unposted Sales Tax Amount',
PAPostedBillingsN 'Actual Billings',
PAPosted_Sales_Tax_Amou 'Actual Sales Tax Amount',
PAPosted_Earnings 'Actual Earnings',
PAActualCostofEarnings 'Actual Cost Of Earnings' ,
PAPostedBillingsN - PAPostedTotalCostN 'Current Actual Profit',
PAProject_Fee_Amount - PAFTotalCost 'Final Forecasted Profits'

from PA01201
where PASTAT = 1
order by 2

Results Example:

Project Name Project number Amount
Dec 1 10024 120
Dec 1A 10024 569
Jal 3 10065 53
Bing 4 10072 100
Bing 4C 10072 564

Each project is shown with each sub project (A, B, C etc) as a seperate amount.

What I want to do is get a total for each project number on 1 line rather than individual amounts.

Example of what I am trying to achieve:

Project Name Project number Amount
Dec 1 10024 689
Jal 3 10065 53
Bing 4 10072 664

Is this possible?

Any advice much appreciated.

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-19 : 07:52:40

"1A" and "4C" is in project number or project name column?

------------------------
PS - Sorry my bad english

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-07-19 : 08:17:54

Can you provide sample data of the PA01201 table in all columns.

We assume, Is this not possible. because you are trying to get all the columns in the same table PA01201. But sum() of should calculate based on the Project Number.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-19 : 09:41:36

so whats the rule for grouping of project number? is it always based on numerical part alone? like 1,1A,1ABC etc falling into same group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources