October 11, 2010 at 1:56 pm
I've been struggling with this query all day and figured if I post here and someone can help, it would be a great opportunity to learn something.
I want to make a SSRS report page that shows supporters and their utilization levels so wrote this query that takes the sums of deciamal values from two tables and presents them along with the total of both.
SELECT SS.SupporterID, SUM(SS.StandardUtilization) AS StandardUtilization
, SUM(PS.ProjectUtilization) AS ProjectUtilization
, SUM(SS.StandardUtilization)+SUM(PS.ProjectUtilization) AS TotalUtilization
FROM StandardSupporter SS JOIN ProjectSupporter PS
ON SS.SupporterID = PS.SupporterID
GROUP BY SS.SupporterID
What I expected to happen is for the report to list a SupporterID, totals for each type of utilization, and then the total of both combined. I've made what seems like a functional query here but it does not give correct results. If there are 23 rows of standard support for a supporter and one row of project support, it is multiplying the project support row by 23.
Can anyone explain why that is and what could be done to give my desired results?
Really appreciate your time.
Howard
October 11, 2010 at 2:17 pm
You are joining the tables, so a single row in the ProjectSupporter is being joined to all 23 rows in the StandardSupporter table, giving you 23 * ProjectUtilization number.
You need to make sure there is a maximum of single row for each before you join.
Here is one way to go after what you want.
select
coalesce(S.SupporterID,p.SupporterID) as SupporterID,
isnull(S.StandardUtilization,0) as StandardUtilization,
isnull(p.ProjectUtilization,0) as ProjectUtilization,
isnull(S.StandardUtilization,0)+isnull(p.ProjectUtilization,0) AS TotalUtilization
from
(
select
SS.SupporterID,
SUM(SS.StandardUtilization) AS StandardUtilization
from
StandardSupporter SS
group by
SS.SupporterID
) s
full outer join
(
select
ps.SupporterID,
SUM(ps.StandardUtilization) AS ProjectUtilization
from
ProjectSupporter PS
group by
PS.SupporterID
) p
on S.SupporterID = p.SupporterID
October 11, 2010 at 3:46 pm
Michael,
This worked perfectly. The technique that you used to create the join is something that I will no doubt need very regularly.
It really does make sense now that I see it presented. I just could not wrap my head around the concept on my own.
Thank you so much for your time and the lession.
Howard
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply