Search code examples
mysqlsqlgroup-byhavingpartition-by

Check if all the values of a column values are same in a partition by query

I want to find if all the values of a column are same in a partition query.

+----------------------------------------+
|b_name |category|indicator |amount|id   |
+-------|--------|----------|------|-----|
|BUCKET1|CAT1    |Y         | 211.8|21006|
|BUCKET2|CAT1    |N         |275.88|21006|
|BUCKET1|CAT2    |Y         | 265.4|21008|
|BUCKET2|CAT2    |Y         | 289.8|21008|
|BUCKET1|CAT3    |N         | 180.6|21011|
|BUCKET2|CAT3    |N         |180.36|21011|
+----------------------------------------+

I want to get the data based on the indicator column with all the values same for group of category and id columns.

the data with same values of indicator and values with 'Y'

+------------------------------------+
|b_name | category | amount | id     |
+------------------------------------+
|BUCKET1| CAT1     | 211.8  | 21006  |
|BUCKET2| CAT1     | 275.88 | 21006  |
|BUCKET1| CAT3     | 180.6  | 21011  |
|BUCKET2| CAT3     | 180.36 | 21011  |
+------------------------------------+

The data with different values of indicator column and (same values of indicator column with 'N' only)

+------------------------------------+
|b_name | category | amount | id     |
+------------------------------------+
|BUCKET2| CAT2     | 275.88  | 21008  |
+------------------------------------+

Please help me with approach to get the data in the format. I want the correct b_name column values for the corresponding row.

Solution

  • One option uses window functions:

    select *
from (
    select t.*,
        min(indicator) over(partition by category, id) min_indicator,
        max(indicator) over(partition by category, id) max_indicator
    from mytable t
) t
where min_indicator = max_indicator

    This gives you groups of rows where there is only one distinct indicator value. You can easily adapt this to filter on a given indicator value:

    where min_indicator = max_indicator and min_indicator = 'Y'

    Or you can filter on groups that have two distinct values:

    where min_indicator <> max_indicator