finding count between two tables

Question

question

Shawn_Melton asked Nov 20, '11

finding count between two tables

I am trying figure out how to query the 1986 MLB Season data that I have to determine the total player count for each team. I have 3 tables: TeamName, Hitter, and Pitcher. The Hitter table is pretty much just the other players on the teams that were not pitchers. I can get the count within each table but I am having troubling trying to figure out how to combine the count for each team. *Alright, here are all the T-SQL statements to create the tables and INSERT for data. I offer no promises I copy/pasted all code without errors.*


CREATE SCHEMA Baseball AUTHORIZATION dbo;
GO

CREATE TABLE Baseball.TeamName
(TeamID int PRIMARY KEY NOT NULL, Abbr varchar(5) NOT NULL, Name varchar(25) NOT NULL);
GO

CREATE TABLE Baseball.Pitcher
(PitcherID int PRIMARY KEY IDENTITY(1,1), Name varchar(25) NOT NULL, TeamID int NOT NULL REFERENCES Baseball.TeamName(TeamID), NumWins int NOT NULL, NumLosses int NOT NULL
	, ERA decimal(5,2) NOT NULL, NumGames int NOT NULL, NumInnings decimal(5,2) NOT NULL, NumSaves int NOT NULL, CareerLength int NOT NULL, CareerWins int NOT NULL
	, CareerLosses int NOT NULL, CareerERA decimal(5,2) NOT NULL, CareerGames int NOT NULL, CareerInnings int NOT NULL, CareerSaves int NOT NULL, Salary money NULL, PlayerTeam1987 int NOT NULL);
GO

CREATE TABLE Baseball.Hitter
(HitterID int PRIMARY KEY IDENTITY(1,1), Name varchar(25), TimesAtBat int NOT NULL, Hits int NOT NULL, HomeRuns int NOT NULL, Runs int NOT NULL, RBIs int NOT NULL
	, Walks int NOT NULL, CareerLength int NOT NULL, CareerTimesAtBat int NOT NULL, CareerHits int NOT NULL, CareerHomeRuns int NOT NULL, CareerRuns int NOT NULL
	, CareerRBIs int NOT NULL, CareerWalks int NOT NULL, TeamID int NOT NULL REFERENCES Baseball.TeamName(TeamID), Position char(3), NumPutOuts int NOT NULL
	, NumAssists int NOT NULL, NumErrors int NOT NULL, AnnualSalary money NULL, PlayerTeam1987 int NOT NULL);
GO

**T-SQL sattements to create the data I'm using. You can also go to my [blog post][5] and get the whole dataset if you want from an Excel spreadsheet.**


INSERT INTO Baseball.TeamName
VALUES (1,'N.Y.','New York')
	, (2,'Phi.','Philadelphia')
	, (3,'St.L.','St. Louis')
	, (4,'Mon.','Montreal')
	, (5,'ChW.','Chicago White Sox')
	, (6,'Pit.','Pittsburgh')
	, (7,'Hou.','Houston')
	, (8,'Cin.','Cincinnati')
	, (9,'S.F.','San Francisco')
	, (10,'S.D.','San Diego')
	, (11,'L.A.','Los Angeles')
	, (12,'Atl.','Atlanta')
	, (13,'Bos.','Boston')
	, (14,'Det.','Detroit')
	, (15,'Tor.','Toronto')
	, (16,'Cle.','Cleveland')
	, (17,'Mil.','Milwaukee')
	, (18,'Bal.','Baltimore')
	, (19,'Cal.','California')
	, (20,'Tex.','Texas')
	, (21,'K.C.','Kansas City')
	, (22,'Oak.','Oakland')
	, (23,'Min.','Minnesota')
	, (24,'Sea.','Seattle')
	, (25,'ChC.','Chicago Cubs');
GO
/* This is just a TOP 20 SELECT on the Pitcher and Hitter Tables */
INSERT INTO Baseball.Hitter (Name,TimesAtBat,Hits,HomeRuns,Runs,RBIs,Walks,CareerLength,CareerTimesAtBat,CareerHits,CareerHomeRuns,CareerRuns,CareerRBIs,CareerWalks,TeamID,Position,NumPutOuts,NumAssists,NumErrors,AnnualSalary,PlayerTeam1987)
VALUES ('Andres Thomas',323,81 ,6,26 ,32 ,8,2,341,86,6,32,34,8,12,'SS',143,290,19 ,75.00,12)
, ('Bob Horner',517,141,27,70,87,52,9,3571,994,215,545,652,337,12,'1B',1378,102,8,0.00,12)
, ('Billy Sample',200,57,6,23,14,14,9,2516,684,46,371,230,195,12,'OF',69,1,1,0.00,12)
, ('Dale Murphy',614,163,29,89,83,75,11,5017,1388,266,813,822,617,12,'CF',303,6,6,1900.00,12)
, ('Glenn Hubbard',408,94,4,42,36,66,9,3573,866,59,429,365,410,12,'2B',282,487,19,535.00,12)
, ('Ken Oberkfell',503,136,5,62,48,83,10,3423,970,20,408,303,414,12,'3B',65,258,8,725.00,12)
, ('Omar Moreno',359,84,4,46,27,21,12,4992,1257,37,699,386,387,12,'RF',151,8,5,0.00,12)
, ('Ozzie Virgil',359,80,15,45,48,63,7,1493,359,61,176,202,175,12,'C',682,93,13,700.00,12)
, ('Rafael Ramirez',496,119,8,57,33,21,7,3358,882,36,365,280,165,12,'S3',155,371,29,875.00,12)
, ('Terry Harper',265,68,8,26,30,29,7,1337,339,32,135,163,128,12,'OF',92,5,3,425.00,14)
, ('Ted Simmons',127,32,4,14,25,12,19,8396,2402,242,1048,1348,819,12,'UT',167,18,6,500.00,12)
, ('Alan Wiggins',239,60,0,30,11,22,6,1941,510,4,309,103,207,18,'2B',121,151,6,700.00,18)
, ('Cal Ripken',627,177,25,98,81,70,6,3210,927,133,529,472,313,18,'SS',240,482,13,1350.00,18)
, ('Eddie Murray',495,151,17,61,84,78,10,5624,1679,275,884,1015,709,18,'1B',1045,88,13,2460.00,18)
, ('Fred Lynn',397,114,23,67,67,53,13,5589,1632,241,906,926,716,18,'CF',244,2,4,0.00,18)
, ('Floyd Rayford',210,37,8,15,19,15,6,994,244,36,107,114,53,18,'3B',40,115,15,0.00,18)
, ('Juan Beniquez',343,103,6,48,36,40,15,4338,1193,70,581,421,325,18,'UT',211,56,13,430.00,21)
, ('Juan Bonilla',284,69,1,33,18,25,5,1407,361,6,139,98,111,18,'2B',122,140,5,0.00,6)
, ('Jim Dwyer',160,39,8,18,31,22,14,2128,543,56,304,268,298,18,'DO',33,3,0,275.00,18)
, ('John Shelby',404,92,11,54,49,18,6,1354,325,30,188,135,63,18,'OF',222,5,5,300.00,18);
GO

INSERT INTO Baseball.Pitcher
(Name,TeamID,NumWins,NumLosses,ERA,NumGames,NumInnings,NumSaves,CareerLength,CareerWins,CareerLosses,CareerERA,CareerGames,CareerInnings,CareerSaves,Salary,PlayerTeam1987)
VALUES ('Jim Acker',12,5,12,4.01,44,155.00,0,4,20,20,3.99,175,411,12,350.00,12)
, ('Doyle Alexander',12,11,10,4.14,34,228.10,0,16,160,135,3.71,467,2709,3,0.00,12)
, ('Paul Assenmacher',12,7,3,2.50,61,68.10,7,1,7,3,2.50,61,68,7,80.00,12)
, ('Jeff Dedmon',12,6,6,2.98,57,99.20,3,4,16,12,3.72,176,270,7,290.00,12)
, ('Gene Garber',12,5,5,2.54,61,78.00,24,17,88,99,3.29,843,1393,194,750.00,12)
, ('Rick Mahler',12,14,18,4.88,39,237.20,0,8,61,59,3.85,216,1084,2,662.50,12)
, ('David Palmer',12,11,10,3.65,35,209.20,0,7,49,36,3.36,157,787,2,725.00,12)
, ('Zane Smith',12,8,16,4.05,38,204.20,1,3,18,26,3.85,83,371,1,150.00,12)
, ('Don Aase',18,6,7,2.98,66,81.20,34,9,61,54,3.74,325,956,75,625.00,18)
, ('Eric Bell',18,1,2,5.01,4,23.10,0,2,1,2,4.97,8,29,0,62.50,18)
, ('Mike Boddicker',18,14,12,4.70,33,218.10,0,7,63,49,3.60,136,900,0,800.00,18)
, ('Rich Bordi',18,6,4,4.46,52,107.00,3,7,17,18,4.00,155,330,10,0.00,18)
, ('Storm Davis',18,9,12,3.62,25,154.00,0,5,54,40,3.65,154,855,1,605.00,10)
, ('Ken Dixon',18,11,13,4.58,35,202.10,0,3,19,18,4.17,71,377,1,155.00,18)
, ('Mike Flanagan',18,0,0,4.50,1,4.00,0,2,1,1,3.32,4,19,0,625.00,18)
, ('Scott McGregor',18,11,15,4.52,34,203.00,0,11,136,98,3.84,326,2038,5,950.00,18)
, ('Oil Can Boyd',13,16,10,3.78,30,214.10,0,5,47,44,3.86,112,791,0,0.00,13)
, ('Roger Clemens',13,24,4,2.48,33,254.00,0,3,40,13,3.15,69,485,0,500.00,13)
, ('Bruce Hurst',13,13,8,2.99,25,174.10,0,7,55,54,4.31,171,1004,0,700.00,13)
, ('Al Nipper',13,10,12,5.38,26,159.00,0,4,31,31,4.35,83,519,0,325.00,13);
GO

[1]: http://meltondba.wordpress.com/tag/Foundation [2]: http://ask.sqlservercentral.com/users/256/williamd/ [3]: http://williamdurkin.wordpress.com/2011/11/17/when-count-isnt-the-only-way-to-count/ [4]: /upfiles/Sum_versus_Count.JPG [5]: http://meltondba.wordpress.com/tag/Foundation

t-sql sql-server-2008-r2 joins

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder commented · Nov 22, 2011 at 03:48 PM

Hi Shawn, I can give you a query that you are looking for but I think the better take-away from this exercise would be to put all of the tables in 3rd normal form. This will make it much easier to retrieve interesting information from the dataset Eg what is the total salary amount for each team. With the current schema you will have to constantly combine the two datasets (hitter/pitcher) and other queries that would normally be straight forward start to get convoluted.

0 ·

Scot Hauder commented · Nov 22, 2011 at 03:59 PM

If you can find data for individual years, that granularity would be better than having columns such as CareerHits--which is a snapshot in time along side data at a different granularity such as salary. This makes it impossible to query for the question of: did the player have more hits when he was making less. If you have per year stats you can also report on interesting things such as who improved the most over the previous year with respect to RBIs. Keep up the blog, this is a good learning experience.

0 ·

Shawn_Melton commented · Nov 22, 2011 at 04:06 PM

Yeah I found this data set off a web site that someone provided it. So on normalization maybe adding table with player name, id, position, and team?

0 ·

Scot Hauder commented · Nov 22, 2011 at 04:13 PM

What you are doing is taking data that is already basically a report (summarized) and trying to get additional useful info from it. I think you will be better served getting data at a more granular level (year or game) and then making reports from that.

0 ·

Shawn_Melton commented · Nov 22, 2011 at 04:23 PM

Well I am just playing around with data to practice making reports and I came across this set that seemed interesting.

0 ·

Answer 1 · 2011-11-24T05:02:50Z

Phil Factor answered Nov 24, '11

SELECT name, COUNT(*) FROM baseball.TeamName INNER JOIN ( SELECT teamID FROM Baseball.Hitter UNION ALL SELECT teamID from Baseball.pitcher) players on players.TeamID=teamname.TeamID GROUP BY baseball.teamname.name

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question