Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sum function where field = Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 2:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 9:30 AM
Points: 5, Visits: 3
Hi,

I am trying to use sum function where a field falls into specific category, but so far no luck.

I have:

Wait_Status column: this can be "missing", "above target" and "within target"
Total: it is counted =Sum(Fields!Surgical_Case_Count.Value)

This Total gives me a number of cases per each wait_status.

What I need is to use the Sum function to get surgical_case_count where wait_status is "within target"...

Any ideas?

Thank you.
Post #1568696
Posted Wednesday, May 7, 2014 3:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 448, Visits: 1,423
You have just written what you need:

masarzdenka (5/7/2014)

Sum ... surgical_case_count where wait_status is "within target"...



select Sum(surgical_case_count)
where wait_status = 'within target'
Post #1568706
Posted Wednesday, May 7, 2014 3:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 9:30 AM
Points: 5, Visits: 3
I am building the report using SQL report builder and "where" is not an option I thought. It doesn't work when I use: =Sum(Fields!Surgical_Case_Count.Value) where (Fields!Wait_Status.Value="Within Target")
Post #1568723
Posted Wednesday, May 7, 2014 3:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 9:30 AM
Points: 5, Visits: 3
I think it needs to be done through lookup, so it looks where wait_status="within target" and takes a value from "surgical_case_count" (which is another column) and sums it up.

I just don't know how to write the expression...
Post #1568725
Posted Thursday, May 8, 2014 6:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:31 AM
Points: 74, Visits: 207
Try using a case statement:

select
Total_within_target = sum(case when Wait_Status.Value="Within Target"
then Fields!Surgical_Case_Count.Value
else 0
end),
Total_missing = sum(case when Wait_Status.Value="Missing"
then Fields!Surgical_Case_Count.Value
else 0
end),
Total_above_target = sum(case when Wait_Status.Value="above_target"
then Fields!Surgical_Case_Count.Value
else 0
end)
from ...
Post #1568852
Posted Thursday, May 8, 2014 7:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 7,120, Visits: 6,978
Try
=Sum(IIf(Fields!Wait_Status.Value="Within Target",Fields!Surgical_Case_Count.Value,0))



Far away is close at hand in the images of elsewhere.

Anon.

Post #1568873
Posted Thursday, May 8, 2014 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 9:30 AM
Points: 5, Visits: 3
This one gives me an error:

=Sum(IIf(Fields!Wait_Status.Value="Within Target",Fields!Surgical_Case_Count.Value,0))

I think I've tried it before.
Post #1568980
Posted Thursday, May 8, 2014 9:33 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:31 AM
Points: 74, Visits: 207
The IIf function is new to SQL Server 2012.
Post #1568981
Posted Thursday, May 8, 2014 9:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 9:30 AM
Points: 5, Visits: 3
David, I am using 2008
Post #1568986
Posted Thursday, May 8, 2014 9:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:31 AM
Points: 74, Visits: 207
Then the case code I gave you should work. I've used similar code many times in 2008 R2. You may have to tweak on the syntax a little.
Post #1568992
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse