SQL Count multiple columns

Question

I have a query that returns multiple columns currently, below is an example.

What I am looking to do is count the number of times ClientID and ServerID pair up. I am looking to get the ServerID that most serves that Client.

ClientID, ServerID,  Last.Date

   1          AB       1/27/2015    
   2          DS       1/27/2015  
   1          JK       1/27/2015
   1          AB       1/24/2015
   2          PD       1/24/2015
   2          DS       1/23/2015

What I want:

ClientID ServerID Last.Date ConnectionCount

1          AB        1/27/2015  2
2          DS        1/27/2015  2

I know I need to use the Count function, but the issue is Count(ClientID+ServerID) is not valid and I am not sure how count based on just two columns.

Thanks ahead of time

~Jason

Answer 1

You can GROUP BY multiple columns, to get the count of each combination.

SELECT ClientID, ServerID, MAX(`Last.Date`) AS `Last.Date`, COUNT(*) AS ConnectionCount
FROM YourTable
GROUP BY ClientID, ServerID

Answer 2

You can use what some call a self-exclusion join where you perform an outer join on a table to itself where you exclude all but one record for each set of matching records.

To do the join, you must first come up with the table that is involved in the join. For your case, the following select statement will serve as the table:

SELECT
  ClientID
, ServerID
, count(*) as ConnectionCount
FROM Table1
GROUP BY ClientID, ServerID;

Using your example data, this will result in the follow result set:

ClientID   ServerID LastDate     ConnectionCount
1          AB       1/27/2015    2
2          DS       1/27/2015    2
1          JK       1/27/2015    1
2          PD       1/24/2015    1

With the above table, we construct the self outer join:

SELECT T1.* FROM
( SELECT
    ClientID
  , ServerID
  , max(LastDate) as LastDate
  , count(*) as ConnectionCount
  FROM Table1
  GROUP BY ClientID, ServerID
) T1
LEFT JOIN
( SELECT
    ClientID
  , ServerID
  , count(*) as ConnectionCount
  FROM Table1
  GROUP BY ClientID, ServerID
) T2
ON (...some on clause...)
WHERE ...some where clause...
;

The ON clause is based on the fact you want to determine the maximum server connection count for each client. To do this you need to compare the connections counts in T1 to the connections counts in T2 by ClientId, or ON (T1.ClientId = T2.ClientId). But that is not a sufficient ON clause to return only the client, server combination with the maximum connections. To do that, you must exclude the records from the join where the T1 connection count is bigger than the T2 connection count for all records. Stated in another way is there exists no T2 row such that the T1 connection count is never smaller than T2 connection count.

With that logic you can come up with the clauses of:

ON (T1.ClientId = T2.ClientId AND T1.ConnectionCount < T2.ConnectionCount)
WHERE T2.ClientId IS NULL

Putting everything together, the final SQL query is:

SELECT T1.* FROM
( SELECT
    ClientID
  , ServerID
  , max(LastDate) as LastDate
  , count(*) as ConnectionCount
  FROM Table1
  GROUP BY ClientID, ServerID
) T1
LEFT JOIN
( SELECT
    ClientID
  , ServerID
  , count(*) as ConnectionCount
  FROM Table1
  GROUP BY ClientID, ServerID
) T2
ON (T1.ClientId = T2.ClientId AND T1.ConnectionCount < T2.ConnectionCount)
WHERE T2.ClientId IS NULL
;