Calculate number of mountpoints

[weretiger]

Hi,

I want to count number of mountpoints appear in the table.

As below sample table,
- Different mountpoints will appear, based on number of server using it. As you can see, the mountpoints will have repeating name based on number of server using it.
- The Count_Mountpoints will calculate number of total Mountpoints appear in the list.


Mountpoints Usedby Count_Mountpoints
/a/ins2 server1 2
/a/ins2 server2 2
/a/ins1 server3 1
/b/ins2 server3 1
/b/ins1 server4 1

I only managed to get number of TOTAL mountpoints.... but by individual's mountpoint total count... help plzzzz

 

[JHB]

You can do it by using 2 queries:
First one, call it "SubMountpoint", remember to change the table name and field name to what you use:

SELECT Mountpoints, Count(Usedby) AS Count_Mountpoints
FROM tblMountpoints
GROUP BY Mountpoints;

Second one:

SELECT tblMountpoints.Mountpoints, tblMountpoints.Usedby, SubMountpoint.Count_Mountpoints
FROM tblMountpoints INNER JOIN SubMountpoint ON tblMountpoints.Mountpoints = SubMountpoint.Mountpoints;

 

[weretiger]

SELECT [Query_HostDetails].Mountpoints, Count([Query_HostDetails].HostName) AS Usedby_Num_Hosts
FROM (SELECT DISTINCT Mountpoints, HostName FROM Query_HostDetails) AS [%$##@_Alias]
GROUP BY [Query_HostDetails].Mountpoints;

the above solved it.... Thank you for the kind help!!