There is a "Project" and a "Measure" table. One or more measures may be related to each project. I want to know all the unique combination of measures related to projects, and how many projects have that unique combination. Then I want to be able to sort the combinations from most to least common.
So, let's say there are six possible measures: A, B, C, D, E, and F. One project may have just measure "A". Another project may have measure "A" and "C". Another project may have measure "B", "D", and "F". I'd like to know all the combinations of measures that exist for the projects. So, for example, there are 10 projects that have the combination "A", "D", and "F". There are five projects that have "C" and "F".
I have a feeling a nested query may be the solution. Any help is appreciated! Thanks.