Find max count of query (SQL Server 2016)

Question

Q1: How do I get a count of NICs for each virtual machine? e.g.

virtualMachine1  2
virtualMachine2  3
virtualMachine3  1
virtualMachine4  2

Q2: How do I get the value that represents the max number of NICs for any virtual machine? e.g.

The following query returns all virtual machine-nic pairs:

SELECT VirtualMachine.VirtualMachineName, VMToNetwork.MacAddress
FROM   VirtualMachine
        INNER JOIN VMToNetwork
         ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID 
ORDER BY VirtualMachine.VirtualMachineName

The field 'VMToNetwork.MacAddress' is not used other than to illustrate that the join worked.

This query attempts to count the number of NICs per virtual machine but simply sums up the total number of NICs.

SELECT count( VMToNetwork.MacAddress )
FROM   VirtualMachine
        INNER JOIN VMToNetwork
          ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID

Thanks.

Add a Group By Clause to group your machines by their machine name — Ryan Wilson, Feb 09 '18 at 21:10

score 0 · Answer 1 · answered Feb 09 '18 at 21:12

Add the group by clause and it will count the values unique to each machine name:

SELECT VirtualMachine.VirtualMachineName, count( VMToNetwork.MacAddress )
FROM   VirtualMachine
        INNER JOIN VMToNetwork
          ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID
        GROUP BY VirtualMachine.VirtualMachineName

NetMage · Accepted Answer · 2018-02-09T21:25:27.243

To get a count of NICs per VM, you must group by VM Name:

SELECT VirtualMachine.VirtualMachineName, count(VMToNetwork.MacAddress) as NICCount
FROM VirtualMachine
INNER JOIN VMToNetwork ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID
GROUP BY VirtualMachine.VirtualMachineName

To get the maximum from that, you can order by NICCount and get the top:

SELECT TOP 1 VirtualMachine.VirtualMachineName, count(VMToNetwork.MacAddress) as NICCount
FROM VirtualMachine
INNER JOIN VMToNetwork ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID
GROUP BY VirtualMachine.VirtualMachineName
ORDER BY NICCount

Note that in a tie, you will only get one of the tie members.

If you just wanted the maximum NIC count, you can also use a subquery:

SELECT MAX(T1.NICCount)
FROM (
    SELECT count(VMToNetwork.MacAddress) as NICCount
    FROM VirtualMachine
    INNER JOIN VMToNetwork ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID
    GROUP BY VirtualMachine.VirtualMachineName
) as T1

Find max count of query (SQL Server 2016)

2 Answers2

Linked