Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

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
0
James Murrell
Asked:
James Murrell
  • 2
1 Solution
 
Scott PletcherSenior 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now