2 Counts über verschiedene Tabellen mit Join

[toxic3008]

Hallo,

ich habe 2 Tabellen:


Tabelle USERS in der alle User aufgeführt sind - jeder User ist einem Team (UserDepartment) zugeordnet!

++++++++++++++++++
+ USERS          +
++++++++++++++++++
+ UserID         +
+ UserName       +
+ UserDepartment +
++++++++++++++++++

Und die Tabelle analytics - Sobald im Tool eine Seite angeklickt wird, wird ein Datensatz mit Uhrzeit, aufgerufene Seite und Abteilung des Mitarbeiters in die Datenbank geschrieben:

++++++++++++++++++
+ ANALYTICS      +
++++++++++++++++++
+ Department     +
+ Time           +
+ Site           +
++++++++++++++++++

Nun habe ich zunächst eine Übersicht, wo alle Teams mit der Anzahl der User aufgeführt sind:

Department  | Users
++++++++++++++++++++
Abteilung 1 |    12
Abteilung 2 |    10
Abteilung 3 |     3


SQL:
SELECT COUNT( * ) AS ZeilenUsers , UserDepartment
FROM users
GROUP BY UserDepartment
ORDER BY ZeilenUsers DESC

Nun möchte ich noch die Pagehits pro Team dahinter geschrieben bekommen, sodass dies dann etwa so ausschaut:

Department  | Users | Pagehits
+++++++++++++++++++++++++++++++
Abteilung 1 |    12 |        
Abteilung 2 |    10 |      123
Abteilung 3 |     3 |       10

Es kann auch sein, dass eine Abteilung gar keine Hits hat, sodass ein Join erforderlich ist!


Folgender Query zählt durch den LEFT-Join leider alle Zeilen, die er durch den Join rausholt:

SELECT COUNT(UserDepartment) AS ZeilenUsers , u.UserDepartment, Count (usersa_team) AS ZeilenVisits
FROM users AS u
LEFT JOIN users_analytics AS a ON (u.UserDepartment=a.usersa_department)
GROUP BY u.UserDepartment
ORDER BY ZeilenUsers DESC

Wie kann man das anders lösen?

Vielen Dank für die Hilfe im Voraus!


Grüße, Stefan

[Indyk]

Hallo Stefan,

obwohl du es so schön vorbildlich gegliedert hast kann ich dir trotzdem nicht foglen. Wo werden die hits gespeichert? und wo selektierst du diese im letzten statement?

und warum zählst du plötzlich die departments als users und nicht die user zum department?

[toxic3008]

Hey,

zur näheren Erläuterung noch mal der Inhalt der Tabellen:


In der Tabele USERS stehen alle User mit der entsprechenden Abteilung!

+++++++++++++++++++++++++++++++++++
+ USERS                           +
+++++++++++++++++++++++++++++++++++
+ UserID + UserName + Department  +
-----------------------------------
+      1 + Name 1   + Abteilung 1 +
+      2 + Name 2   + Abteilung 1 +
+      3 + Name 3   + Abteilung 1 +
+      4 + Name 4   + Abteilung 2 +
+      5 + Name 5   + Abteilung 2 +
+      6 + Name 6   + Abteilung 3 +
+      7 + Name 7   + Abteilung 4 +
+++++++++++++++++++++++++++++++++++

Pro Zugriff auf eine Seite wird ein Datensatz in die Analytics Tabelle geschrieben.
Hierzu wird die Abteilung festgehalten, der Timestamp und welche Seite dies war!

++++++++++++++++++++++++++++++++++++++++
+ ANALYTICS                            +
++++++++++++++++++++++++++++++++++++++++
+ Department  + Time      + Site       +
----------------------------------------
+ Abteilung 1 + 189123123 + home.php   +
+ Abteilung 1 + 189123125 + users.php  +
+ Abteilung 1 + 189123127 + test.php   +
+ Abteilung 1 + 189123129 + xyz.php +  +
+ Abteilung 2 + 189123123 + home.php   +
+ Abteilung 2 + 189123125 + users.php  +
+ Abteilung 2 + 189123127 + test.php   +
+ Abteilung 4 + 189123129 + xyz.php +  +
++++++++++++++++++++++++++++++++++++++++

Die Ergebnistabelle, die ich gerne bekommen möchte, sieht so aus:

++++++++++++++++++++++++++++++
+ Department  + Users + Hits + 
++++++++++++++++++++++++++++++
+ Abteilung 1 +     3 +    4 + 
+ Abteilung 2 +     2 +    3 + 
+ Abteilung 3 +     1 +    - + 
+ Abteilung 4 +     1 +    1 +
++++++++++++++++++++++++++++++

Habe das SQL-Statement noch mal ein bisschen kommentiert (im alten war eine Feldbezeichnung falsch):

SELECT 
  COUNT(U.UserDepartment) AS NumUsers, # Anzahl der User/Abteilung
  U.UserDepartment, # Abteilungsname
  Count (A.Usersa_department) AS Pagehits # Abzahl der Pagehits

FROM users AS U

LEFT JOIN Users_analytics AS A # nicht jedem Team müssen Hits zugeordnet sein --> LEFT JOIN
  ON (U.UserDepartment=A.Usersa_department)

GROUP BY U.UserDepartment #Anzeigen der einzelnen Abteilungen
ORDER BY NumUsers DESC # Sortierung nach User/Abteilung

Hoffe, dass mein Problem jetzt ein bisschen verständlicher ist.

Vielen Dank!

Grüße