Sql query to get for each element a group string that has the maximum value

Question

the question is hard to summarize in the title, so here is a more detailed example:

I have a huge data set of dozens of dimensions for thousands of different objects. Most of them have a related type, but this type is not unique.

So select Select

SELECT oid, type, count(type) FROM data GROUP BY oid, type;

will create something like:

oid   type  count(type)
 0      0   22
 1      0   22
 2      1   61
 2      2  104
 3      2   63
 4      0   34
 6      0    1
 8      2   76
 9      0    1
11      3   33
12      0   55
13      4    1
13      5   28
13      1    2
13      2  255
14      4  148
14      1    4
14      2    3
15      3   10
16      0   13
18      4  137
18      1    5

How can I get only one row per object for the result if this single row should be the one with the most cases?

Bonus question: Also get the percentage on one line of the object, which represents the appearance coefficient of this type.

The result should look like this:

oid   type  P(type)
 0      0   1.0
 1      0   1.0
 2      2   0.64
 3      2   1.0
 4      0   1.0
 6      0   1.0
 8      2   1.0
 9      0   1.0
11      3   1.0
12      0   1.0
13      2   0.89
14      4   0.95
15      3   1.0
16      0   1.0
18      4   0.96

edit:

some test data and the almost correct conclusion of one solution:

http://pastebin.com/jVvHErJ2

Answer 1

SELECT s.oid,
       s.type,
       s.total_per_oid_per_type,
       (s.total_per_oid_per_type + 0.0) / s.total_per_oid AS percentage
FROM (SELECT v.oid,
             v.type,
             v.total_per_oid_per_type,
             ROW_NUMBER() OVER (PARTITION BY v.oid ORDER BY v.total_per_oid_per_type DESC) AS object_number,
             SUM(v.total_per_oid_per_type) OVER (PARTITION BY v.oid) AS total_per_oid
      FROM (SELECT t.oid, t.type, count(1) AS total_per_oid_per_type
            FROM data t
            GROUP BY t.oid, t.type) v ) s
WHERE object_number = 1

, Sqlite3 ( )

WITH v AS (
    SELECT oid,
           type,
           COUNT(1) AS total_per_oid_per_type
    FROM data
    GROUP BY oid, type
),
s AS (
    SELECT oid,
           MAX(total_per_oid_per_type) AS max_total_per_oid
    FROM v
    GROUP BY oid
),
totals AS (
    SELECT oid,
           SUM(total_per_oid_per_type) AS total_per_oid
    FROM v
    GROUP BY oid
)
SELECT v.oid,
       v.type,
       v.total_per_oid_per_type,
       (v.total_per_oid_per_type + 0.0) / totals.total_per_oid AS percentage
FROM v
     INNER JOIN s ON v.oid = s.oid AND v.total_per_oid_per_type = s.max_total_per_oid
     INNER JOIN totals ON v.oid = totals.oid
ORDER BY v.oid, v.type

Answer 2

,

create table ##TBL (oid INT, [type] INT, [count(type)] INT)
INSERT INTO ##TBL VALUES
(0,0,22),
(1,0,22),
(2,1,61),
(2,2,104),
(3,2,63),
(4,0,34),
(6,0,1),
(8,2,76),
(9,0,1),
(11,3,33),
(12,0,55),
(13,4,1),
(13,5,28),
(13,1,2),
(13,2,255),
(14,4,148),
(14,1,4),
(14,2,3),
(15,3,10),
(16,0,13),
(18,4,137),
(18,1,5)
--------------------------------

SELECT oid
      ,max([type]) as x
      --,Max([count(type)]) AS [count(type)]
      ,CAST( CAST( MAX([count(type)]) AS DECIMAL(10,2) ) /  CAST( SUM([count(type)]) AS DECIMAL(10,2) ) AS DECIMAL(10,2) ) AS 'Percent %'
from ##TBL
group by oid