consolidate column values only if same-CodePudding

I have a table with the following structure:

category | col_a | col_b
--------- ------- ------
cat_1    |   1.0 | X
cat_1    |   0.0 | X
cat_1    |   1.0 | Y
cat_2    |   2.0 | Z
cat_2    |   1.0 | Z

Values in col_a (float values) need to be simply summed - no issues here. However, I want to consolidate all col_b (strings) values only if all values are the same per category, otherwise return null. So final output should be like below:

category | sum_a | same_or_none_b
--------- ------- ---------------
cat_1    |   2.0 | null
cat_2    |   3.0 | Z

Can someone kindly help or direct me towards a solution? Thank you.

CodePudding user response：

You could use a CASE expression to distinguish the two cases:

SELECT category,
       sum(col_a) AS sum_a,
       CASE WHEN count(DISTINCT col_b) = 1
            THEN min(col_b)
       END AS same_or_none_b
FROM tab
GROUP BY category;