SQL to Count # of distinct entries

Databases
1

I have a database that tracks the date, player and his partners.
CREATE TABLE TeamMate(
TeamMateID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
TeamMatePlayDate TEXT,
TeamMatePlayerName TEXT,
TeamMatePartnerName TEXT );

CREATE INDEX TeamMatePlayerPartnerIx ON TeamMate (TeamMatePlayerName, TeamMatePartnerName, TeamMatePlayDate);

If Player 1 played with 3 others, 3 entries would be placed in the database:

Date Player Partner
2016-02-25 Player 1 Player 2
2016-02-25 Player 1 Player 3
2016-02-25 Player 1 Player 5

I want to summarize for each player how many times he played with all other partners:

Player Name Partner Name Count
Player 1 Player 2 5
Player 1 Player 3 7
Player 1 Player 5 2
Player 2 Player 1 5
Player 2 Player 5 3 …etc

The following SQL gives me all partners “Player 1” played with since the beginning of the year.

SELECT DISTINCT TeamMatePartnerName FROM TeamMate WHERE
TeamMatePlayerName = “Player 1” AND TeamMatePlayDate >= “2016-01-01”

How to I also get the count for each player and partner?

Jim

2

I’m gonna go with:

SELECT TeamMatePlayerName, TeamMatePartnerName, Count(TeamMateParnterName) FROM TeamMate WHERE TeamMatePlayDate >= '2016-01-01' GROUP BY TeamMatePartnerName ORDER BY TeamMatePlayerName

3

Groupby and Orderby should be

4

Thanks, that works great. The ‘GROUP BY’ was what I was missing.

I’ll be able to use this in several other places.

Thanks again,

Jim

5

Thanks Dave, I’ll add that to the SQL

Jim