Count(case when) redshift sql - receiving groupby error

Question

I'm trying to do a count(case when) in Amazon Redshift.

Using this reference, I wrote:

select
sfdc_account_key,
record_type_name,
vplus_stage,
vplus_stage_entered_date,
site_delivered_date,

case when vplus_stage = 'Lost' then -1 else 0 end as stage_lost_yn,
case when vplus_stage = 'Lost' then 2000 else 0 end as stage_lost_revenue,
case when vplus_stage = 'Lost' then datediff(month,vplus_stage_entered_date,CURRENT_DATE) else 0 end as stage_lost_months_since,
count(case when vplus_stage = 'Lost' then 1 else 0 end) as stage_lost_count


from shared.vplus_enrollment_dim
where record_type_name = 'APM Website';

But I'm getting this error:

[42803][500310] [Amazon](500310) Invalid operation: column "vplus_enrollment_dim.sfdc_account_key" must appear in the GROUP BY clause or be used in an aggregate function; java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: column "vplus_enrollment_dim.sfdc_account_key" must appear in the GROUP BY clause or be used in an aggregate function;

Query was running fine before I added the count. I'm not sure what I'm doing wrong here -- thanks!

score 0 · Answer 1 · answered Apr 16 '19 at 20:06

You can not have an aggregate function (sum, count etc) without group by

The syntax is like this

select a, count(*)
  from table
 group by a (or group by 1 in Redshift)

In your query you need to add

group by 1,2,3,4,5,6,7,8

because you have 8 columns other than count

Since I don't know your data and use case I can not tell you it will give you the right result, but SQL will be syntactically correct.

score 0 · Answer 2 · answered Apr 16 '19 at 21:42

The basic rule is:

If you are using an aggregate function (eg COUNT(...)), then you must supply a GROUP BY clause to define the grouping
Exception: If all columns are aggregates (eg SELECT COUNT(*), AVG(sales) FROM table)
Any columns that are not aggregate functions must appear in the GROUP BY (eg SELECT year, month, AVG(sales) FROM table GROUP BY year, month)

Your query has a COUNT() aggregate function mixed-in with non-aggregate values, which is giving rise to the error.

In looking at your query, you probably don't want to group on all of the columns (eg stage_lost_revenue and stage_lost_months_since don't look like likely grouping columns). You might want to mock-up a query result to figure out what you actually want from such a query.

Count(case when) redshift sql - receiving groupby error

2 Answers2