Solved

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

Posted on 2015-02-09
4
112 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
  • 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 65

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

765 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