SQL query with sum and unique output

Question

Let me base my query from the table here: SQL query with distinct and sum

This table, slightly edited:

medley_id   | class    |  color   |   fruit   |   rating
========================================================
1             sweet       red         apple       25
2             sour        blue        pear        5
3             sweet       green       apple       12
4             sour        red         apple       10
5             sour        purple      kiwi        5
6             sweet       purple      kiwi        50
7             sour        blue        kiwi        3
8             sour        blue        pear        9

I want a query that yields this result:

class       |   color   |   fruit   |   sum
============================================
(multiple)      red         apple       35
sour            blue        pear        14
sour            blue        kiwi        3
sweet           green       apple       12
(multiple)      purple      kiwi        55

(multiple) doesn't have to tell me what the multiple is, it just has to know that it's multiple, but it's not something that makes the row unique in terms of what the sum is.

Does that make sense? Can anyone help? Thanks!

Answer 1

I think this might do it:

SELECT
  CASE
    WHEN COUNT(DISTINCT class) > 1 THEN '(multiple)'
    ELSE MAX(class)
  END AS class
, color
, fruit
, SUM(rating) AS sum
FROM MyTable
GROUP BY color,fruit

Untested, but it should be possible to shape this idea into a working solution.

Answer 2

Without using GROUP BY and only windowed functions:

SELECT DISTINCT
[class] = CASE WHEN 
             DENSE_RANK() OVER (PARTITION BY color, fruit ORDER BY class) 
            +DENSE_RANK() OVER (PARTITION BY color, fruit ORDER BY class DESC) - 1 > 1
              THEN '(multiple)'
              ELSE class
           END
  ,color
  ,fruit
  ,[sum]=  SUM(rating) OVER (PARTITION BY color, fruit)   
FROM #medleys

LiveDemo

Output:

╔════════════╦════════╦═══════╦═════╗
║   class    ║ color  ║ fruit ║ sum ║
╠════════════╬════════╬═══════╬═════╣
║ (multiple) ║ purple ║ kiwi  ║  55 ║
║ (multiple) ║ red    ║ apple ║  35 ║
║ sour       ║ blue   ║ kiwi  ║   3 ║
║ sour       ║ blue   ║ pear  ║  14 ║
║ sweet      ║ green  ║ apple ║  12 ║
╚════════════╩════════╩═══════╩═════╝