Count query for GROUP BY returns wrong value

63 views
Skip to first unread message

Hung Tran

unread,
Feb 9, 2016, 2:07:52 PM2/9/16
to OrientDB
Hi,

The SQL was working fine on OrientDB version 1.7.8 but not working right in version 2.1.8.


SELECT count(*) FROM ContactAction WHERE (Type=0 AND Campaign=#47:16) GROUP BY Contact

In version 1.7.8, it returns 9 as a single value, that's a correct value.

In version 2.1.8, it returns 9 rows and count by each contact instead (see photo below)



Any help will be appreciated!

My Best,
Hung Tran

user.w...@gmail.com

unread,
Feb 9, 2016, 3:09:58 PM2/9/16
to OrientDB
Hi Hung,

I tried your query and I don't have any type of problem.






Regards,

Michela

Hung Tran

unread,
Feb 10, 2016, 3:56:50 AM2/10/16
to orient-...@googlegroups.com
Hi Michela,

Thank you for your tried. Which database version are you using? How many different contacts are there in your testing table?

My Best,
Hung Tran

alessand...@gmail.com

unread,
Feb 10, 2016, 6:14:40 AM2/10/16
to OrientDB
Hi, 
I have made a test with the following records and the query works. I'm using OrientDB 2.1.8.

insert into ContactAction(Type,Campaign,Contact) values (0,#47:16,#38:0)
insert
into ContactAction(Type,Campaign,Contact) values (0,#47:16,#38:0)
insert
into ContactAction(Type,Campaign,Contact) values (0,#47:16,#38:0)
insert
into ContactAction(Type,Campaign,Contact) values (0,#47:16,#38:0)
insert
into ContactAction(Type,Campaign,Contact) values (0,#47:16,#38:0)


insert
into ContactAction(Type,Campaign,Contact) values (0,#47:16,#38:1)
insert
into ContactAction(Type,Campaign,Contact) values (0,#47:16,#38:1)



Best regards,
Alessandro

Hung Tran

unread,
Feb 10, 2016, 7:28:42 AM2/10/16
to orient-...@googlegroups.com
Hi Alessandro,

That's what i mean, it does not work when you have different contacts.

In your database, if the SQL is

SELECT Contact, count(*) FROM ContactAction GROUP BY Contact

it should return like your photo, but if I remove the Contact projection, it should return 2 instead of 2 rows of count per Contact => the query works but the result set is wrong.

If it's still unclear, please see the different SQLs as following

SELECT count(*) FROM ContactAction => 7 ~ total rows in ContactAction

SELECT count
(*) FROM ContactAction GROUP BY Contact => 2 ~ number of contacts having any ContactAction row

SELECT
Contact, count(*) FROM ContactAction GROUP BY Contact => 2 rows of [contact id, number of ContactAction row by contact id]

They have different meanings. The 2nd query does not work as expected (it is returning a similar result set as the 3rd query)

A workaround solution for 2nd query in earlier versions (strictSql = false)

SELECT count(*) FROM (SELECT FROM ContactAction GROUP BY Contact)

My Best,
Hung Tran

alessand...@gmail.com

unread,
Feb 10, 2016, 8:39:44 AM2/10/16
to OrientDB
Sorry, I misunderstood.

You can also use the following query:

SELECT count(distinct(Contact)) FROM ContactAction

Best regards,
Alessandro
Reply all
Reply to author
Forward
0 new messages