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
 Sum aggregate function problem

Author  Topic 

senenami
Starting Member

1 Post

Posted - 2007-06-30 : 21:15:20
Hi,

I've the following query:
select F_Mode, Issue_Count, Sum(Issue_Count) as IssueSum, milestone from rpt_fact_Paretos
Group By F_Mode having milestone = 'SDS'

Sample Data:
F_Mode Issue_Count milestone
Fail1 5 SDS
Fail2 6 SDS

If the above query is executed, the resultset is:
Fail1 5 5 SDS
Fail2 6 6 SDS

Question: I'm not getting the SUM of Issue_Count. I agree that I'm using F_Mode as Group By and hence the result. But if I use milestone as Group By I won't get all the F_Mode in the result set.

I'm looking for a result like this:

Fail1 5 11 SDS
Fail2 6 11 SDS

How can I achievce this?

Thanks in advance,
SD

mageshks
Yak Posting Veteran

59 Posts

Posted - 2007-07-01 : 00:23:39
Hope this would give you the desired result

declare @IssueSum int
select @IssueSum=sum(Issue_Count) from rpt_fact_Paretos
where milestone = 'SDS'


select F_Mode, Issue_Count,@IssueSum as IssueSum , milestone from rpt_fact_Paretos
Group By F_Mode,Issue_Count,milestone having milestone = 'SDS'
Go to Top of Page

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2007-07-01 : 04:17:10
[code]SELECT F_Mode
,Issue_Count
,IssueSum = (
SELECT SUM(Issue_Count)
FROM rpt_fact_Paretos iFP
WHERE iFP.milestone = oFP.milestone)
,milestone
FROM rpt_fact_Paretos oFP
WHERE milestone = 'SDS'[/code]

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page
   

- Advertisement -