CODEWITHSUNDEEP
sqldategroup-bysubquerynetezza
tlswpsl
tlswpsl

Reputation: 97

Basic SQL question for Group By (in Netezza)

This may sound stupid, but I'm having trouble with my SQL code for Group By in Netezza that I cannot seem to figure out. Basically, I'm doing simple sum and am trying to group the results, which is where I'm coming across issues. Current table looks like: 

id      date     daily_count
---------------------------
1      4/1/20        2
1      4/2/20        1
2      4/1/20        3
2      4/1/20        2
2      4/3/20        1

I want to make it to looks like: 

id      date     daily_count
---------------------------
1      4/1/20        2
1      4/2/20        1
2      4/1/20        5
2      4/3/20        1

my select statement is:

select id, date, sum(count) over (partition by date, id) as daily_count

If I do group by clause including the sum field (group by id, date, daily_count), I get warning saying:

Windowed aggregates not allowed in a GROUP BY clause

But if I exclude sum field in group by clause (group by id, date), then I get warning saying:

Attribute count must be GROUPed or used in an aggregate function

count is the variable that I'm summing, so if I group that, it won't produce the right sum amount.

Does this mean that grouping has to happen outside of this query, meaning cte or subquery? I'm hoping to get some advice to know what exactly is happening and what is the best course of action.

Upvotes: 1

Views: 400

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You seem to just want aggregation:

select id, date, sum(count) as daily_count
from t
group by id, date;

I'm not sure why you are trying to use a window function here.

Upvotes: 2

GMB
GMB

Reputation: 222672

You want simple aggregation rather than window functions:

select id, date, sum(daily_count) daily_count
from mytable
group by id, date

Upvotes: 2

Related Questions