7

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

Improve this question
4
  • So you want a count of the combination of Client ID and Server ID? Commented Jan 30, 2015 at 2:10
  • You can group the query by serverid. An example of group aggregation is. mysql> SELECT student_name, AVG(test_score) -> FROM student -> GROUP BY student_name; just replace avg with count or something similar Commented Jan 30, 2015 at 2:11
  • Why have you chosen 1/27/2015 for the 1,AB pairing, over 1/24/2015. Do you want MAX(Last.Date) too? Commented Jan 30, 2015 at 2:11
  • Sorry the date part was mostly just to show extra data, but Yes mjsqu the MAX(Last.Date) is required for the next step. Shree.pat18 yes I am looking for the count of the combination of clientID and server ID Commented Jan 30, 2015 at 2:13

4 Answers 4

Reset to default
11

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks Barmar, this is worked but the issue I am running into is this is not being run on one table. I am trying to add this to a query I already have running that spams multiple tables and has a GROUP BY already in it. I am trying to figure out how to do this in the middle of a query. I feel like this is close to what I am looking for though.
You probably just need to put that as a subquery in place of YourTable. But I'm just guessing. Update the question and show what you really need, I'm not a mind reader.
Barmar, Thanks I looked at putting it in as a subquery and it appears to be working.
6

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
;
Improve this answer

Comments

5

you can use group by and get maximum connections and then further do one more group by on the client to get the server that serves the most

SQL Fiddle

SELECT clientId, serverId,
max(connectionCount) as ConnectionCount, LastDate
from
(
select clientId, ServerID, 
count(*) as ConnectionCount,
max(LastDate) as LastDate
from Table1
group by clientId, ServerID
  ) t
group by clientId
Improve this answer

1 Comment

The second group by is insufficient as it will not return the server with the maximum amount of connections. It simply returns the first server for each client. To verify, add two records with client 1 and server JK at the end of the data. Running the query still results in AB as the maximum for client 1 when it is clearly JK. sqlfiddle.com/#!2/2e872/1
0 
select ClientID,ServerID,Last.Date,count(ServerID) as count 
From your table
group by ServerID
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.