Count in einer Abfrage

huberttdm859

Grünschnabel
Hi,
ich hab bereit im WWW schon eine wenig gestöbert aber für mein Problem noch keine Lösung gefunden.
Ich habe folgende Abfrage:
Select * from Terminpl
Where (tbz >='20.10.2007 00:00:00' and tvz <='01.11.2007 23:59:59')
order by IDBO ASC

Nun möchte ich ein weiteres Feld Anzahl haben, das mit das Feld idbo (idbo = benutzerid) summiert.
Kurz erklärt: es ist eine Termindatenbank in der jeder Benutzer seine Termine speichert. Diese Abfrage macht einfach einen zeitlichen auszug der Termine.
Ich hätte nun gern die Anzahl dabei wieviele Termine der Benutzer pro tag in dieser Abfrage hat.

Das Ergebnis sollte so aussehen:

ID_Termin, idBo, ..spalten.... , tvz, tbz, Anzahl
1----------------6 .......................,'20.10.2007 8:00:00', 20.10.2007 8:20:00' , 3
2----------------6 .......................,'20.10.2007 9:00:00', 20.10.2007 10:00:00' , 3
3----------------6 .......................,'20.10.2007 11:00:00', 20.10.2007 13:10:00', 3
4----------------26 .....................,'22.10.2007 13:00:00', 22.10.2007 14:10:00', 1
5----------------5 .......................,'21.10.2007 08:00:00', 20.10.2007 12:00:00', 2
6----------------5 .......................,'29.10.2007 13:00:00', 20.10.2007 16:00:00', 2

wie man sieht, sollte einfach die Anzahl der Datenesätze eines Benutzers (idbo) gezählt werden.

Vielleicht kann mir da jemand weiterhelfen!


DANKE

gruß
hubert
 
Angenommen die Spalte "user" existiert:
SQL:
SELECT *,COUNT(`user`) AS `idBo` 
FROM `Terminpl`
WHERE (tbz >='20.10.2007 00:00:00' and tvz <='01.11.2007 23:59:59') 
GROUP BY `user`
ORDER BY `idBo` ASC;

Ungetestet...
 
Zuletzt bearbeitet von einem Moderator:
Hi,
leider funktioniert dieses SQL beim MS SQL-Server nicht!

Ich bin schon verzweifelt. Ich hab schon so vieles ausprobiert und bin noch auf keinen grünen Zweig gekommen.:confused:

Wue gesagt, die abfrage sollte lediglich ein zusäätzliches Feld haben, dass die Datensätze des entsprechenen Benutzers zählt.
z.B.:
ID_Termin, idBo, ..spalten.... , tvz, tbz, Anzahl
1----------------6 .......................,'20.10.2007 8:00:00', 20.10.2007 8:20:00' , 3
2----------------6 .......................,'20.10.2007 9:00:00', 20.10.2007 10:00:00' , 3
3----------------6 .......................,'20.10.2007 11:00:00', 20.10.2007 13:10:00', 3

Der Benutzer mit der IDBO=6 hat als Ergebnis 3 Datensätze. Ich bräuchte das Feld Anzahl mit der 3 als Inhalt.

Vielleicht gibts ja gar keine Lösung?


Gruß
hubert
 
Ups wusste ich nicht....
Sicher gibts da ne Lösung.
Welches Wort kennt MS SQL denn nicht und dann musst du gucken wie es eben in MS SQL heißt, sind ja nur leichte Unterschiede.
 
Danke für die Antwort

Ich hab das Statement angepasst:

SELECT *,COUNT(idbo) AS 'idBo'
FROM Terminpl WHERE (tbz >='20.10.2007 00:00:00' AND tvz <='01.11.2007 23:59:59')
GROUP BY idbo
ORDER BY idBo ASC

Das Feld `user` ist bei mir idbo.

Der Server gibt mir aber als Antwort:
************************************************
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.idtp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.idste' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.idfkt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.idgru' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.gfnr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.bonc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.kz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.rnr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.adr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.ort' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.plz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.de' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.besvork' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.statwert' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.beschlag' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.adz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.fc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.fc2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.fc3' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.angabe' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.stand' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.garb' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.bearb' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.amnr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.bemerkung' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.ueberlcount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.term' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.termfertig' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.username' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.tvz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.tbz' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Terminpl.changedate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
************************************************

Das sind alle Felder der Tabelle.
Anscheinend will er alle felder im GROUP BY Befehl haben!

Verstehst Du das

gruß
hubert
 
Servus.

Wenn Du ne Aggregatsfunktion, also COUNT, AVG, SUM, ... in deinem SELECT Teil hast, müssen alle Felder, welche NICHT Teil einer dieser Funktionen sind, in der GROUP BY Klausel aufgeführt werden. Die Reihenfolge sollte dort aber egal sein.

Gruß

M.
 

Neue Beiträge

Zurück