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