October 7, 2008 at 3:40 am
I have a problem which is prob very simple but it's driving me mad!
I've four tables
Users Targets Placements PlacementConsultant
Users Targetid Placementid Placementid
Userid Userid PlacementFee Userid
username Boardings
surname Month
I'm trying to write a query that sums the boarding from the target table and placementfee from the placement table and group it by each user.
The problem I'm having is that the boarding col is being multiplied by the amount of times that the userid appears in the placementconsultant table
select u.username +' '+u.surname,sum(total_boarded), sum(placementfee), count(p.placementid) from targets t
join users u on u.userid = t.userid
left join placementconsultants pc on pc.userid = u.userid
join placements p on p.placementid = pc.placementid
group by u.username,u.surname
Thanks in advance
Dave
October 7, 2008 at 5:58 am
dcarpenter (10/7/2008)
select u.username +' '+u.surname,sum(total_boarded), sum(placementfee), count(p.placementid) from targets tjoin users u on u.userid = t.userid
left join placementconsultants pc on pc.userid = u.userid
join placements p on p.placementid = pc.placementid
group by u.username,u.surname
Use the same (combined) columns in you GROUP BY as you use in your SELECT.
GROUP BY u.username +' '+u.surname
instead of username and surname seperated.
October 7, 2008 at 6:46 am
I don't think Hanshi's solution is correct. Could you supply some sample data along with the desired result? You may need to use a derived table or a CTE in order to get the data you desire. You could also try using Sum(Distinct value).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 7, 2008 at 7:08 am
What relationship is there, if any, between placement fees and boardings?
If there isn't one, you're going to end up with, effectively, a cross-join, which will give you row multiplication.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 7, 2008 at 8:16 am
There’s isn't a relationship between boarding’s and placementfee.
I'm trying to show what sales people have achieved against what they are targeted to achieve
October 7, 2008 at 8:29 am
Here is how I think I'd solve the problem, but without some data I can't guarantee accuracy:
[font="Courier New"];WITH cteUserTargets AS
(
SELECT
U.userid,
U.username + ' ' + u.surname AS username,
SUM(T.boardings) AS total_boardings
FROM
dbo.users U JOIN
dbo.targets T ON
U.userid = T.userid
GROUP BY
U.userid
),
cteUserPlacements AS
(
SELECT
PC.userid,
COUNT(P.placementid) AS placements,
SUM(P.placementfee) AS total_fees
FROM
dbo.placements P JOIN
dbo.placementconsultant PC ON
P.placementid = PC.placementid
GROUP BY
U.userid
)
SELECT
C.username,
C.totalboardings,
P.placements,
P.total_fees
FROM
cteUserTargets C LEFT JOIN
cteUserPlacements P ON
C.userid = P.userid
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply