mercredi 30 septembre 2015

Microsoft SQL Server 2012: Algorithm to do a summation over a column with random selection of data from other column

I have a table like this:

CREATE TABLE Table1
    ([IdeaNr] int, [SubmitterName] varchar(4), [SubmitterDepartment] varchar(4))
;

INSERT INTO Table1
    ([IdeaNr], [SubmitterName], [SubmitterDepartment])
VALUES
    (1, 'Joe', 'Org1'),
    (1, 'Bill', 'Org2'),
    (1, 'Kate', 'Org1'),
    (1, 'Tom', 'Org3'),
    (2, 'Sue', 'Org2'),
    (3, 'Bill', 'Org2'),
    (3, 'Fred', 'Org1'),
    (4, 'Ted', 'Org3'),
    (4, 'Kate', 'Org1'),
    (4, 'Hank', 'Org3')
;

I want get the following result from a query:

IdeaNr  SubmitterCount   SubmitterRndName   SubmitterRndDepartment
1       4                Joe or ...         Org1 (if Joe)
2       1                Sue                Org2
3       2                Bill or ...        Org1 (if Bill)
4       3                Ted or ...         Org3 (if Ted)

I have tried a lot of thinks with all kind of JOINs of Table1 with itself, derived tables and GROUP BY, e.g.:

SELECT COUNT(IdeaNr) AS SubmitterCount,IdeaNr,SubmitterName,SubmitterDepartment
FROM Table1
GROUP BY IdeaNr,SubmitterName,SubmitterDepartment

I think the problem is to create an algorithm that takes just the first (or a random) name and department appearing in a group of IdeaNr. It is absolutely clear that you can get to misleading interpretations of that kind of data, e. g.:

  • Org1 has 2 Ideas
  • Org2 has 1 Idea
  • Org3 has 1 Idea

But this kind of "wrong averaging" is OK for the task. Can you help?




Aucun commentaire:

Enregistrer un commentaire