2

I need to perform a group by, but only on rows that meet a condition, otherwise return all rows that do not meet the condition. For example, in the following table, I want to group only rows that have '1' in the "active" field, and return all rows that do not.

TABLE (id, label, active):

1, A, 1
2, A, 1
3, B, 0
4, B, 0

Would return:

1, A, 1
3, B, 0
4, B, 0
John Saunders
  • 160,644
  • 26
  • 247
  • 397
scader
  • 405
  • 3
  • 8
  • 19

3 Answers3

3

Here is the simplest way I can think of. It is a case statement in the group by. If the condition is met then group by the label, otherwise group by the primary key.

SELECT id, label, active
FROM table
GROUP BY
  CASE
    WHEN active = 1 THEN active
    ELSE id END

If you want to group by active and label:

SELECT id, label, active
FROM table
GROUP BY
  CASE
    WHEN active = 1 THEN active
    ELSE id END,
  label

EDIT: I misunderstood which field you want to group on. Corrected now.

Jackson Miller
  • 1,500
  • 1
  • 13
  • 23
0

You could use a CASE statement

select min(id) as id, label, active
from (
     select id, label, active,
           case
             when active = 1 then 'active'
             else convert(varchar,newid()) as discriminator
           end
     from table) t
group by label, active, discriminator

this will give a common value to all rows that meet the condition (active = 1) and a unique value to all other rows so that they remain ungrouped.

I don't have access to SQL server at the moment so this is completely untested.

tvanfosson
  • 524,688
  • 99
  • 697
  • 795
  • So... SELECT id, label, active, case when active = 1 then null else convert(varchar,newid()) end as groupit group by groupit Unfortunately, I will be using aggregate functions on a lot of other fields that are also returned, but I think I can get the raw data to group first as a sub select, and then join the miscellaneous data. – scader Jan 30 '10 at 14:06
  • I prefer my method since there is no subquery and repurposing the id field as the unique option in the case statement reduces a couple of function calls. However, if going with this one I think you have to remove the id column from the GROUP BY clause. Otherwise it will return all rows with no grouping. – Jackson Miller Jan 30 '10 at 14:24
0

Perhaps the layout of the query could be something like this:

select
  min(id),
  label,
  max(active)
from
  Foo
where
  active = 1
group by
  label

union

select
  id,
  label,
  active
from
  Foo
where
  active = 0
Christoffer Lette
  • 14,346
  • 7
  • 50
  • 58