Count query for GROUP BY returns wrong value

[Hung Tran]

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]

Hi Hung,

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

Regards,

Michela

[Hung Tran]

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]

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]

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]

Sorry, I misunderstood.

You can also use the following query:

SELECT count(distinct(Contact)) FROM ContactAction

Best regards,

Alessandro