SQL Query to sort distinct records by number of duplicates

Question

I have a table of tasting notes, and I want to create a wordcloud with the biggest words corresponding to the most-often-mentioned words in the following table, ideally also considering their priority.

tbTastingNote

ID                   tasteCat   priority    value
Blender's Reserve    Finish     1           Nutmeg
Blender's Reserve    Nose       1           Vanilla
Blender's Reserve    Palate     1           Apple
Blender's Reserve    Nose       2           Hazelnut
Blender's Reserve    Palate     2           Almond
Distiller's Reserve  Finish     1           Almond
Distiller's Reserve  Nose       1           Vanilla
Distiller's Reserve  Palate     1           Apple
Distiller's Reserve  Nose       2           Biscuits
Distiller's Reserve  Palate     2           Nutmeg
Distiller's Reserve  Nose       3           Nutmeg

The wordcloud part is sorted as it simply outputs the largest word first, second-largest second and so on. So I need the table to be queried such that it creates distinct records sorted descending by the sum of their value of (4 - priority); eg Apple should have a score of 6 (mentioned twice as priority 1), Nutmeg has a score of 6 (1 x P1, 1 x P2 and 1 x P3) and biscuits has a score of 2 (mentioned just once, as a P2).

Can anyone suggest an SQL statement that will accomplish this?

Answer 1

So I need the table to be queried such that it creates distinct records sorted descending by the sum of their value of (4 - priority)

This looks like a simple aggregate query:

select value, sum(4 - priority) as score
from tbTastingNote
group by value
order by score desc