I have a data base that I need to query to count and sum the number of points generated by a pass from a player in a basketball game. For instance, if a player passes to a teammate and that pass results in 2 points, my data base currently stores that as 1 instance in certain table. There is another table that the data is stored in if the pass resulted in 3 points. I would like to query so that all instances of a pass from a player that results in 2 points are counted and then multiplied by 2, and all instances of a pass from a player that results in 3 points are counted and then multiplied by 3.
Here are my relevant tables and select statements:
CREATE TABLE Passer(
PasserID int identity PRIMARY KEY not null
, Forename char(30) not null
, Surname char (30) not null)
CREATE TABLE Teammate(
TeammateID int identity PRIMARY KEY not null
, Forename char(30) not null
, Surname char(30) not null
, PasserID int FOREIGN KEY REFERENCES Passer(PasserID) not null)
CREATE TABLE TwoPointsFromShot(
TwoPointsFromShotID int identity PRIMARY KEY not null
, PasserID int FOREIGN KEY REFERENCES Passer(PasserID) not null
, TeammateID int FOREIGN KEY REFERENCES Teammate(TeammateID) not null)
CREATE TABLE ThreePointsFromShot(
ThreePointsFromShotID int identity PRIMARY KEY not null
, PasserID int FOREIGN KEY REFERENCES Passer(PasserID) not null
, TeammateID int FOREIGN KEY REFERENCES Teammate(TeammateID) not null
--First and Last Name of Passer from TwoPointsFromShot--
SELECT Forename, Surname
FROM Passer
JOIN TwoPointsFromShot ON TwoPointsFromShot.PasserID = Passer.PasserID
--First And Last name of Passer from ThreePointsFromShot--
SELECT Forename, Surname
FROM Passer
JOIN ThreePointsFromShot ON ThreePointsFromShot.PasserID = Passer.PasserID
When I query the PasserID from the TwoPointsFromShot table, I receive a table that looks like:
| PasserID
------------------- | --------
1 | 1
2 | 3
3 | 3
4 | 2
I receive a similar table when querying PasserID from ThreePointsFromShot table.
| PasserID
--------------------- | --------
1 | 3
2 | 1
3 | 3
4 | 4
I would like to return a query that counts the number of instances of the PasserID from TwoPointsFromShot and multiplies that by 2, counts the number of instances of the PasserID from ThreePointsFromShot and multiples that by 3, sums the two values, and replaces the PasserID with the name of the player. So it would look like this (if Julius Randle is PlayerID 1, Dario Saric is PlayerID 2, TJ McConnell is PlayerID 3, and Brandon Ingram is PlayerID 4):
| PasserName | PointsFromTwo | PointsFromThree | PassToPoints
--- | --------- | --------------- | ------------------ | ------------
1 | Julius Randle | 2 | 3 | 5
2 | Dario Saric | 2 | 0 | 2
3 | TJ McConnell | 4 | 6 | 10
4 | Brandon Ingram| 0 | 3 | 3
Any help would be greatly appreciated!
I was able to get the results you want by using two common table expressions.
--CTE to get number of 2 pointers
WITH twoPointers AS
(
SELECT PasserId,COUNT(*) '2ptCount'
FROM TwoPointsFromShot
GROUP BY PasserID
),
--CTE to get number of 3 pointers
threePointers As
(
SELECT PasserId,COUNT(*) '3ptCount'
FROM ThreePointsFromShot
GROUP BY PasserID
)
--Join the Passer table with 2 CTE's and
--calculate results
SELECT RTRIM(Forename) + ' ' + RTRIM(Surname) AS 'PasserName',
ISNULL(two.[2ptCount] * 2, 0.00) AS 'PointsFromTwo',
ISNULL(three.[3ptCount] * 3, 0.00) AS 'PointsFromThree',
ISNULL(two.[2ptCount] * 2, 0.00) + ISNULL(three.[3ptCount] * 3, 0.00) AS 'PassToPoints'
FROM Passer p
LEFT JOIN twoPointers two ON p.PasserID = two.PasserID
LEFT JOIN threePointers three ON p.PasserID = three.PasserID