Solved

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

Posted on 2015-02-09
4
116 Views
Last Modified: 2015-02-09
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
Comment
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
  • Learn & ask questions
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 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

by:Jim Horn
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

by:James Murrell
ID: 40599324
Thanks could not see the wood for the trees on that one
0
 
LVL 69

Expert Comment

by:Scott Pletcher
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question