SQL using count in case statement

Question

I'm trying to group all activity where the count is less than 5 (for data sharing reasons).

The ID Code and ID Name give me high level numbers, but as soon as I include "shop code" I get some low level numbers for customers that go to odd shops once or twice.

Select  Count(*) [Activity],
        T.[ID Code],
        T.[ID Name],
        Case when Count(*) < 6 then 'Other Shop' Else T.Shop End [Shop Code]
From    MyTable T
Group By T.[ID Code],
         T.[ID Name],
         Case when Count(*) < 6 then 'Other Shop' Else T.Shop End

But obviously I can't use a count in a case statement. I've tried some of the solutions to similar questions but none of them work!

Thanks

case expression, not statement.

jarlh
– jarlh

2017-04-12 11:12:22 +00:00
Commented Apr 12, 2017 at 11:12 — jarlh
– jarlh, Commented Apr 12, 2017 at 11:12

Gordon Linoff · Accepted Answer · 2017-04-12 11:10:11Z

1

The problem is the GROUP BY:

Select  Count(*) as [Activity],
        T.[ID Code],
        T.[ID Name],
        (Case when Count(*) < 6 then 'Other Shop' Else T.Shop
         End) as [Shop Code]
From    MyTable T
Group By T.[ID Code],
         T.[ID Name];

Aggregate functions (or expressions with aggregates) don't belong in the GROUP BY. These are calculated in the SELECT, not used to define groups.

answered Apr 12, 2017 at 11:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Leandro Franciscato · Accepted Answer · 2017-04-12 11:17:06Z

0

You can use the HAVING and UNION ALL statement, like this:

Select  Count(*) as [Activity],
    T.[ID Code],
    T.[ID Name],
    'Other Shop' [Shop Code]
 From MyTable T
Group By T.[ID Code],
      T.[ID Name]
having Count(*) < 6
 union all
Select  Count(*) as [Activity],
    T.[ID Code],
    T.[ID Name],
    T.Shop [Shop Code]
 From MyTable T
Group By T.[ID Code],
      T.[ID Name]
having Count(*) >= 6

answered Apr 12, 2017 at 11:17

Leandro Franciscato

265 bronze badges

Comments

Anurag Dadheech · Accepted Answer · 2017-04-12 11:24:10Z

0

select 
count(*) as activity,
code,
name,
Case when Count(*) < 6 then 'Other Shop' Else shopcode End as shopcode
from mytable group by code, name ,shopcode

answered Apr 12, 2017 at 11:24

Anurag Dadheech

6291 gold badge5 silver badges15 bronze badges

Comments

LukStorms · Accepted Answer · 2017-04-12 12:29:30Z

The example below is a test in SQL Server.

It uses a window function for count, to change the Shop code.
And then groups it all, including that modified shopcode.

declare @ShopTable table ([ID Code] varchar(30), [ID Name] varchar(30), Shop varchar(30));

insert into @ShopTable ([ID Code], [ID Name], Shop) values 
('S1','Shop 1','AA'),
('S1','Shop 1','BB'),
('S1','Shop 1','BB'),
('S1','Shop 1','CC'),
('S1','Shop 1','CC'),
('S1','Shop 1','CC'),
('S2','Shop 2','XX'),
('S2','Shop 2','YY');

select 
count(*) as [Activity], 
[ID Code], 
[ID Name], 
[Shop Code]
from (
  select
   [ID Code],
   [ID Name],
   case when count(*) over (partition by [ID Code], [ID Name]) < 6 then 'Other Shop' else Shop end as [Shop Code]
  from @ShopTable
) Q
group by [ID Code], [ID Name], [Shop Code];

Collectives™ on Stack Overflow

SQL using count in case statement

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related