Count (*) and group by

Hi. This questions is related to the other one concerned about distinct values. I have a relationship N:1 between 2 entities -> VDI and Host. Y select a VDI but then I create another entity where the Host and the VDI are specified. I must select the Host that has the lowest load of the VDI with the same name. So then, in SQL I have the following query: select, anyHostWithTheVDI.srvIP from _Host anyHostWithTheVDI, _VDI theVDI where'NAMEOFVDI' AND theVDI.deployedToday='1' AND AND not exists (select * from (SELECT count( ) as hostUsage, AS hostID, h.srvDNSname, h.capacity FROM _Host h, _VDI vdi, _VMusage vmusg WHERE vmusg.vdi = AND = AND vdi.deployedToday=1 AND vmusg.state != 'history' AND (('STARTDATEOFVDI' BETWEEN vmusg.start AND vmusg.plannedstop) OR ('PLANNEDSTOP' BETWEEN vmusg.start AND vmusg.plannedstop) OR ('STARTDATEOFVDI' < vmusg.start AND vmusg.plannedstop < PLANNEDSTOP))) GROUP BY hostWithTheVDIinUse where hostWithTheVDIinUse.hostUsage>=hostWithTheVDIinUse.capacity AND ) Then I get the host and I create the VMUSAGE object but I don't know how to obtain this information within a microflow. Any idea to obtain the solution?
1 answers

Replacing SQL by a microflow can be done, but it will not always perform satisfying.

You can start by replacing the SELECT count( ) with a retrieve and a list aggregate (count). For the other select add the conditions as in the sql statement. And as Pieter says: please explain the purpose of the query.