February 10, 2020 at 5:24 pm
All,
I'm trying to teach myself grouping sets.
I have an existing database giving me a result I don't understand but when I create a test example it gives me the result I expect and I can't work out why there is a difference. The test example is as follows:
create table test (favouritecount int, viewcount int, commentcount int)
insert into test (favouritecount , viewcount , commentcount ) values (148,67525,5)
select favouritecount, viewcount, sum(commentcount)
from test
group by grouping sets (
(favouritecount, viewcount ),
(favouritecount),
(viewcount)
)
order by favouritecount, viewcount
This gives me three rows, one of each grouping set, which is the result I expected:
FavouriteCount ViewCount CommentCount
NULL 67525 5
148 NULL 5
148 67525 5
If I run the following query on the existing database:
select favoritecount, viewcount, sum(commentcount)
from posts
where CreationDate >'01/01/2019'
group by grouping sets (
(favoritecount, viewcount ),
(favoritecount),
(viewcount)
)
order by favoritecount, viewcount
I get the following two rows:
FavoriteCount ViewCount CommentCount
148 NULL 5
148 67525 5
I can't work out why one row is missing. There are too many rows in the posts table to create a script so I ran the following query which, I think, shows that the data is the same as the test table:
select favoritecount, viewcount, commentcount
from posts
where (CreationDate>'01/01/2019')
and (favoritecount = 148
or viewcount =67525)
Result:
FavoriteCount, ViewCount,CommentCount
148 67525 5
The only difference I can see between the queries is the where clause for the 'creationdate' and I don't think that would affect the number of grouping sets displayed? The reason for the where clause is to narrow down the search to one table partition, I'm working on a system with not many resources.
Apologises if I've missed a simple difference between the queries? If there is no simple difference then I would appreciate some suggestions on what I can check to try and work out the reason for the different results. I checked the column types and they are the same.
February 10, 2020 at 5:30 pm
All,
Sorry - please ignore this question. A simple mistake reading the results on the posts table. The query works as expected. I had been looking at it for a couple of hours but didn't find it until after posting.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply