max und count

hallo zusammen,

in meiner datenbank gibt es eine tabelle person, eine tabelle aufgaben und eine tabelle projekte. jede person bekommt nun mehrere aufgaben zugeteilt die jeweils zu einem projekt gehören.
ich will nun alle namen abfragen und dazu das hauptprojekt dieser personen. das hauptprojekt soll das sein, von dem die person die meisten aufgaben hat.

meine idee war, die aufgaben mittels eines counts zu zählen und dann mittels eines max jeweils nur das projekt darzustellen welches die meisten zähler hatt. leider funktioniert das gar nicht so wie ich mir das gedacht hab.

vielleicht könnt ihr mir ja helfen

Leider ist nicht klar, wie Du das abbildest. Ich vermute also mal:

test=*# \d person
                         Table "public.person"
 Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------
 id     | integer | not null default nextval('person_id_seq'::regclass)
 name   | text    |
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)

test=*# \d projekt
                         Table "public.projekt"
 Column |  Type   |                      Modifiers
--------+---------+------------------------------------------------------
 id     | integer | not null default nextval('projekt_id_seq'::regclass)
 name   | text    |
Indexes:
    "projekt_pkey" PRIMARY KEY, btree (id)

test=*# \d aufgaben
                            Table "public.aufgaben"
    Column    |  Type   |                       Modifiers
--------------+---------+-------------------------------------------------------
 id           | integer | not null default nextval('aufgaben_id_seq'::regclass)
 person       | integer |
 projekt      | integer |
 beschreibung | text    |
Indexes:
    "aufgaben_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "aufgaben_person_fkey" FOREIGN KEY (person) REFERENCES person(id)
    "aufgaben_projekt_fkey" FOREIGN KEY (projekt) REFERENCES projekt(id)

test=*# select * from person;
 id |  name
----+---------
  1 | müller
  2 | meier
  3 | schulze
(3 rows)

test=*# select * from projekt;
 id |    name
----+------------
  1 | programm 1
  2 | programm 2
(2 rows)

test=*# select * from aufgaben;
 id | person | projekt |  beschreibung
----+--------+---------+-----------------
  2 |      1 |       1 | Doku schreiben
  3 |      1 |       1 | programm testen
  4 |      1 |       1 | fehler finden
  5 |      2 |       1 | code schreiben
  6 |      2 |       2 | code schreiben
  7 |      2 |       2 | fehler suchen
  8 |      2 |       1 | doku schreiben
(7 rows)

test=*#



meine idee war, die aufgaben mittels eines counts zu zählen und dann mittels eines max jeweils nur das projekt darzustellen welches die meisten zähler hatt. leider funktioniert das gar nicht so wie ich mir das gedacht hab.

vielleicht könnt ihr mir ja helfen

Schnellschuß:

test=*# select distinct on (projekt) projekt, person, aufgaben from (
  select projekt, person, count(1) as aufgaben from aufgaben group by projekt, person
) foo 
  order by projekt, person, aufgaben desc;
 projekt | person | aufgaben
---------+--------+----------
       1 |      1 |        3
       2 |      2 |        2
(2 rows)

Hoffe, das hilft Dir und hoffe auch, daß Du PG hast, denn das DISTINCT ON ist PG-spezifisch.

Andreas

perfekt, das wars

vielen dank

mh, also so ganz funktioniert es leider doch nicht.
bei mir taucht im ergebnis leider immer nur die erste aufgabe auf
die einer person zugeteilt wurde.

select * from aufgaben
 person  | projekt | abteilung
---------+--------+------------------
  john    |     web |  ....
  robert  |     web |  ...
  robert  |    prog | ...
  huber   |    prog | ....
  bjoern  |     web | ...
  bjoern  |     web | ...
  bjoern  |    prog |  ..

egal was ich mach, das beste was ich bekomm ist das hier:

SELECT DISTINCT ON (person) projekt, person aufgaben

FROM 	(SELECT project, person, COUNT(1) AS aufgaben
				FROM aufgaben 
				GROUP BY projekt, person) foo  

person  | projekt | aufgaben
---------+--------+------------------
  bjoern  |     prog |  2
  hubert  |     prog |  1
  ......

eigentlich müsste bei björn “web” dran stehen und nicht “prog”.
er nimmt also immer das was im alphabet als erstes steht und nicht das, was auch am meisten vorkommt.
wenn ich statt

DISTINCT ON (person)

ein

DISTINCT ON (projekt)

mach, dann stehen leider nicht alle namen dran.



test=*# select * from aufgaben ;
 person | projekt
--------+---------
 john   | web
 robert | web
 robert | prog
 huber  | prog
 bjoern | web
 bjoern | web
 bjoern | prog
(7 rows)

test=*# select distinct on (person) person, aufgaben, projekt from (select person, projekt, count(1) as aufgaben from aufgaben group by 1,2 ) foo order by person, aufgaben desc,projekt;
 person | aufgaben | projekt
--------+----------+---------
 bjoern |        2 | web
 huber  |        1 | prog
 john   |        1 | web
 robert |        1 | prog
(4 rows)

Andreas

super,
jetzt funktionierts auch so wie ich das wollte.

vielen vielen dank

Rechnung für artek

g

Andreas