Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2015-02-09
Medium Priority
124 Views
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
``````

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
Question by:James Murrell
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 40599126
SELECT     SUM(res.points * CASE WHEN  r.round = f1.round THEN 1 ELSE 2 END) AS points, f1.Driver1
...
0

LVL 66

Expert Comment

ID: 40599253
Give us an example of 'double the points', as that's hard to read when the numbers are 701, 726, 18, and 25.
0

LVL 31

Author Closing Comment

ID: 40599324
Thanks could not see the wood for the trees on that one
0

LVL 70

Expert Comment

ID: 40599329
Not necessary instinctive, but now that you've seen that technique, you'll use it easily again in the future.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
###### Suggested Courses
Course of the Month8 days, 16 hours left to enroll