CODEWITHSUNDEEP
mysqlsqlsqlite
greenForrest
greenForrest

Reputation: 13

SQL: Sort by number of same values in one column without aggregating/contracting

I have a table of objects and their types in a table as follows:

objectType | object

107          Boeing
107          Airbus
323          Audi
323          Mercedes
323          BMW
985          Ducati

I would like to compute a results table as shown below where the groupSizeOfObject column is shown for each row and represents the number of objects in the table with the same objectType as the object in that row. For example, there are three objects with objectType 323; Audi, Mercedes, and BMW, and so each of the three rows should have a 3 in the groupSizeOfObjectType column.

objectType | object   | groupSizeOfObjectType

323          Audi       3
323          Mercedes   3
323          BMW        3
107          Boeing     2
107          Airbus     2
985          Ducati     1

Use of the group by and count(*) directives allows me to compute the following, but the individual object rows have been grouped into one result row as follows:

objectType | object   | groupSizeOfObjectType (for clarification, optional)

323          Audi       3
107          Boeing     2
985          Ducati     1

In the results, I need to have all objects.

How can I achieve this?

My actual problem is a little more complicated. Given I have a table with an extra objectKind column as follows:

objectType | objectKind |  object

107          B             Boeing
107          B             Airbus
323          D             Audi
323          D             Mercedes
323          D             BMW
323          D             Lexus
985          B             Ducati

I would like to compute the columns representing the counts of both objectType and objectKind, and then order the resultant rows first by objectKind, and then objectTypes, both in descending order producing the following result:

objectType | objectKind |  object


323          D             Audi      
323          D             Mercedes
323          D             BMW
323          D             Lexus
107          B             Boeing
107          B             Airbus
985          B             Ducati

Upvotes: 0

Views: 55

Answers (2)

dnoeth
dnoeth

Reputation: 60482

You can calculate the counts either in Derived Tables (see other answers) or using Scalar Subqueries (fiddle):

SELECT o.*,
  ( SELECT COUNT(*)
    FROM object as ok
    WHERE ok.objectKind = o.objectKind
  ) as kind_cnt,
  ( SELECT COUNT(*)
    FROM object as to
    WHERE ot.objectType= o.objectType
  ) as type_cnt
FROM object o
ORDER BY kind_cnt DESC, type_cnt DESC;

If you con't need to display the counts:

SELECT *
FROM object o
ORDER BY
  ( SELECT COUNT(*)
    FROM object as ok
    WHERE ok.objectKind = o.objectKind
  )  DESC, 
  ( SELECT COUNT(*)
    FROM object as ot
    WHERE ot.objectType= o.objectType
  ) DESC

Upvotes: 1

Arth
Arth

Reputation: 13110

You need to know how many of each kind and type there are before you start ordering the rows. This can be achieved by calculating these in separate queries and joining the result on to the original table.

  SELECT o.*
    FROM object o
    JOIN (
      SELECT objectKind, COUNT(*) count
        FROM object   
    GROUP BY objectKind
         ) ok
      ON ok.objectKind = o.objectKind
    JOIN (
      SELECT objectType, COUNT(*) count
        FROM object   
    GROUP BY objectType
         ) ot
      ON ot.objectType= o.objectType
ORDER BY ok.count DESC, ot.count DESC, o.object ASC

SQLFiddle Demo

Upvotes: 1

Related Questions