Group by in SQL returning error: Selected non-aggregate values must be part of the associated group

Question

I have a table that looks like this:

       date      store   flag
1    5/4/2018      a       1
2    5/4/2018      a       1
3    5/3/2018      b       1
4    5/3/2018      b       0
5    5/2/2018      a       1
6    5/2/2018      b       0

I want to group by date and store and sum the number of flags

i.e. table_a below:

       date      store   total_flag
1    5/4/2018      a       2
3    5/3/2018      b       1
4    5/2/2018      a       1
5    5/2/2018      b       0

This is what I'm trying:

create multiset volatile table flag_summary as (
 sel table_a.*, SUM(table_a.flag) as total_flag
 group by date, store
)
with data primary index (date, store) on commit preserve rows;

The above gives me an error, "CREATE TABLE Failed. [3504] Selected non-aggregate values must be part of the associated group.

Answer 1

You are selecting all of tableA (including the flag). You should just be pulling the date and the store since you want the sum of the flag.

SELECT date, store, SUM(flag)
FROM tableA
GROUP BY date, store