Custom aggregation in GROUP BY clause

Question

If I have a table with a schema like this

table(Category, SubCategory1, SubCategory2, Status)

I would like to group by Category, SubCategory1 and aggregate the Status such that if not all Status values over the group have a certain value Status will be 0 otherwise 1.

So my result set will look like

(Category, SubCategory1, Status)

I don't want to write a function. I would like to do it inside the query.

Answer 1

You can test that both the minimum and maximum status for each group are equal to your desired value:

SELECT
    category,
    subcategory1,
    CASE WHEN MIN(status) = 42 AND MAX(status) = 42 THEN 1 ELSE 0 END AS Status
FROM table1
GROUP BY category, subcategory1

Answer 2

Let's say you want to find groups that have all status values under 100

SELECT category, subcategory1,
    CASE WHEN MAX(status) < 100 THEN 0 ELSE 1 END AS Status
FROM table1
GROUP BY category, subcategory1

All groups with status under 100 will have Status set to 0, and all groups with at least one status >= 100 will be set to 1.

I think that's what you're asking for, but if not let me know.

Answer 3

Assuming that status is a numeric data type, use:

  SELECT t.category, 
         t.subcategory1,
         CASE WHEN MIN(t.status) = MAX(t.status) THEN 1 ELSE 0 END AS status
    FROM dbo.TABLE_1 t
GROUP BY t.category, t.subcategory1

Answer 4

I would like to group by Category, SubCategory1 and aggregate the Status such that if not all Status values over the group have a certain value Status will be 0 otherwise 1.

I'm interpreting this as "If there exists a Status value in a given group not equal to a given parameter, the returned Status will be 0 otherwise 1".

Select T.Category, T.SubCategory1
    , Case
        When Exists(
                    Select 1
                    From Table As T2
                    Where T2.Category = T.Category
                        And T2.SubCategory1 = T.SubCategory1
                        And T2.Status <> @Param
                    ) Then 0
        Else 1
        End As Status
From Table As T
Group By t.Category, T.SubCategory1

Answer 5

Something like that :

select 
  Category,
  SubCategory1,
  (
    case 
      when good_record_count = all_record_count then 1 
      else 0
    end 
  )                    as all_records_good
  from (
    select 
      t.Category,
      t.SubCategory1,
      sum( cast(coalesce(t.Status, 'GOOD', '1', '0') as int) ) good_record_count,
      count(1)                                                 all_record_count
     from 
       table_name t
     group by 
       t.Category, t.SubCategory1
  )