GROUP BY with JOIN produces unexpected results
Hello,
I am using MonetDB in a university project dealing with analysis of twitter data.
"stuff" contains some pre-selected user ids. "tweets1207" contain tweet data for one day (userid, latitude, longitude, time, ....)
What I want in the end is to find all (rounded) unique geo-coordinates of tweets by users in "stuff". I am using the following query:
SELECT userid, ROUND(latitude,0) AS lat, ROUND(longitude,0) AS long FROM tweets1207 INNER JOIN stuff USING (userid) WHERE latitude <> 0 AND longitude <> 0 GROUP BY userid, lat, long ORDER BY userid, lat, long;
The result set however still contains rows with duplicate values for "lat" and "long", e.g.
userid lat long ----------------------------- 19703 41 -74 19703 41 -74
If I remove the INNER JOIN part from the query, the grouping works as expected.
To check whether I am doing something fundamentally wrong (don't have much experience with SQL), I tried running the same query in a sqlite version of the data, where it produced the result I expected (although much, much slower...)
Can anybody point me in the right direction? Am I doing something wrong here?
Best regards and thanks in advance, Jonas
I'm not sure, but try to do the group by on the round On 2 Dec 2014 03:57, "Jonas Goerlich" jonas.goerlich@gmail.com wrote:
Hello,
I am using MonetDB in a university project dealing with analysis of twitter data.
"stuff" contains some pre-selected user ids. "tweets1207" contain tweet data for one day (userid, latitude, longitude, time, ....)
What I want in the end is to find all (rounded) unique geo-coordinates of tweets by users in "stuff". I am using the following query:
SELECT userid, ROUND(latitude,0) AS lat, ROUND(longitude,0) AS long FROM tweets1207 INNER JOIN stuff USING (userid) WHERE latitude <> 0 AND longitude <> 0 GROUP BY userid, lat, long ORDER BY userid, lat, long;
The result set however still contains rows with duplicate values for "lat" and "long", e.g.
userid lat long
19703 41 -74 19703 41 -74
If I remove the INNER JOIN part from the query, the grouping works as expected.
To check whether I am doing something fundamentally wrong (don't have much experience with SQL), I tried running the same query in a sqlite version of the data, where it produced the result I expected (although much, much slower...)
Can anybody point me in the right direction? Am I doing something wrong here?
Best regards and thanks in advance, Jonas
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
I just found out that there is a new release "october2014". Installing it fixed my problem.. was it really a bug then?
On Tue, Dec 2, 2014 at 5:54 AM, amihay gonen agonenil@gmail.com wrote:
I'm not sure, but try to do the group by on the round
On 2 Dec 2014 03:57, "Jonas Goerlich" jonas.goerlich@gmail.com wrote:
Hello,
I am using MonetDB in a university project dealing with analysis of twitter data.
"stuff" contains some pre-selected user ids. "tweets1207" contain tweet data for one day (userid, latitude, longitude, time, ....)
What I want in the end is to find all (rounded) unique geo-coordinates of tweets by users in "stuff". I am using the following query:
SELECT userid, ROUND(latitude,0) AS lat, ROUND(longitude,0) AS long FROM tweets1207 INNER JOIN stuff USING (userid) WHERE latitude <> 0 AND longitude <> 0 GROUP BY userid, lat, long ORDER BY userid, lat, long;
The result set however still contains rows with duplicate values for "lat" and "long", e.g.
userid lat long
19703 41 -74 19703 41 -74
If I remove the INNER JOIN part from the query, the grouping works as expected.
To check whether I am doing something fundamentally wrong (don't have much experience with SQL), I tried running the same query in a sqlite version of the data, where it produced the result I expected (although much, much slower...)
Can anybody point me in the right direction? Am I doing something wrong here?
Best regards and thanks in advance, Jonas
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
amihay gonen -
Jonas Goerlich