Forgot to mention that ! So sorry, Version 2.0 M3 on linux On Thursday, December 18, 2014 7:06:16 PM UTC, Enrico Risa wrote: > > Hi syshex > > which version of ODB do you have ? > > > > 2014-12-18 18:32 GMT+01:00 syshex <[email protected] <javascript:>>: >> >> Hi everyone. >> >> Have a schema that goes somewhat along these lines : >> >> ( Institute ) <---owner --- ( Contract ) --- hired ---> ( Company ) >> >> I'm trying to create an SQL query that shows all companies that have >> contracts with a particular institute and tells me how many contracts each >> company has. I've tried several different ways , but probably due to lack >> of experience I'm just not being able to get it. >> >> I've tried with traversal and other, but I basically got to this point >> now : >> >> 1- With this query I can get all companies that have a contract with a >> particular institute >> >> select >> EXPAND(in('owner').out('hired')) >> from Institute where itemid = 47 >> limit 10000 >> >> It gives me the list of all companies , with an overall record count of >> 162. (this is important, the record count!) >> >> 2- next I try to expand that query to start grouping by and counting : >> >> select >> EXPAND(in('owner').out('hired')) >> from Institute where itemid = 47 >> group by description >> limit 10000 >> >> I was expecting to get a list of fewer records ( because some companies >> have multiple contracts with this Institute ) . But what I got back was 0 >> Records >> >> I though : OK , the EXPAND function is ran after the group by and >> therefore the queries being grouped by at that point have no property named >> "description", just the id . >> >> 3- Instead of group by, I though : let me try the order by clause to see >> what happens >> >> select >> EXPAND(in('owner').out('hired')) >> from Institute where itemid = 47 >> order by description DESC >> limit 10000 >> >> I get a list of 162 records again , all ordered perfectly , showing me >> several repeated records , therefore, the several times a company was hired >> by that institute. >> >> 4 - Because I have a list of 162 records with this query, I though about >> using it as a sub-query of another select statement, and perform the group >> by and count() with that select. >> >> select >> description, count(*) as hiredTimes >> from ( >> select >> EXPAND(in('owner').out('hired')) >> from Institute where itemid = 47 >> limit 10000 >> ) >> group by description >> order by hiredTimes DESC >> limit 10000 >> >> It returns me 112 records , of which none has a hiredTimes > 1, which >> tells me that for this select only 112 records were evaluated. >> >> 5 - I decided to try running this previous select without the group by >> clause, just to make sure >> >> select >> * >> from ( >> select >> EXPAND(in('owner').out('hired')) >> from Institute where itemid = 47 >> limit 10000 >> ) >> limit 10000 >> >> I get a list of records, but only 112 of them . >> >> I must be missing something. Should I by going about this in a different >> way ? >> >> Thanks much for your help. >> >> -- >> >> --- >> You received this message because you are subscribed to the Google Groups >> "OrientDB" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> For more options, visit https://groups.google.com/d/optout. >> >
-- --- You received this message because you are subscribed to the Google Groups "OrientDB" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
