working out how to double sum of one round if rounds match

Hi hope you can help I have a query

SELECT     SUM(res.points) AS points, f1.Driver1
FROM         races AS r INNER JOIN
                      results AS res ON r.raceId = res.raceId INNER JOIN
                      f1users AS f1 ON res.driverId = LEFT(f1.Driver1, 3)
WHERE     (r.year = 2014) AND (f1.submissionID = 51)
GROUP BY f1.Driver1, r.round

Open in new window


which show the total points for a year but what i now need is to double the points if r.round = f1.round  so at mo

it would show

701 Lewis Hamiltion

but new one would show

726 Lewis Hamilton as round 18 matches so he collects a extra 25 points
LVL 31
James MurrellProduct SpecialistAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT     SUM(res.points * CASE WHEN  r.round = f1.round THEN 1 ELSE 2 END) AS points, f1.Driver1
...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us an example of 'double the points', as that's hard to read when the numbers are 701, 726, 18, and 25.
0
 
James MurrellProduct SpecialistAuthor Commented:
Thanks could not see the wood for the trees on that one
0
 
Scott PletcherSenior DBACommented:
Not necessary instinctive, but now that you've seen that technique, you'll use it easily again in the future.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.