Hi there, I migrated a relational database from MySQL to OrientDB but I have an issue in producing a query and I hope to have an help from community.
Consider two tables named drug and demo and suppose I launch the
following query in MySQL with aggregation:
select role_cod, count(de.caseid) as count
from drug dr
join demo de on (dr.primaryid = de.primaryid)
where lower(drugname) like '%paracetamol%'
group by role_cod
Attribute role_cod represents role of a drug: I would to count all cases
(the attiribute caseid represents a single case) in which paracetamol is
reported, aggregate by role.
Table drug and demo are joined by an attribute called primaryid. For
every one row in the demo table, we can have one or more rows in the
drug tables.
In OrientDB I created to class named drug and demo linked by edge of
class named drugToDemo.
If I launch the following query in OrientDB I obtain the same correct:
select role_cod, count($caseid) as count
from drug
let $caseid = in('drugToDemo').caseid
where drugname.toLowerCase() like '%paracetamol%'
group by role_cod
But if I would to obtain all *distinct* cases, in MySQL I launch
follwing query:
select role_cod, count(distinct de.caseid) as count
from drug dr
join demo de on (dr.primaryid = de.primaryid)
where drugname.toLowerCase() like '%paracetamol%'
group by role_cod
Now, I don't know how to represent this query in OrientDB. I tried the
following query using the distinct() function:
select role_cod, count(distinct($caseid)) as count
from drug
let $caseid = in('drugToDemo').caseid
where drugname.toLowerCase() like '%paracetamol%'
group by role_cod
but the composition of count and distinct functions don't produce the
same results of count obtained in MySQL. In this case I obtain always 1
as count and I think it is always the only result obtainable.
Any ideas?
Thank you.
--
Fabio Rinnone
Skype: fabiorinnone
Web: http://www.fabiorinnone.eu
--
---
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.
signature.asc
Description: OpenPGP digital signature
