[Gelöst]Count in Reports

Hallo Zusammen,

ich möchte in einem Report die Anzahl der offenen und Geschlossenen Anfragen zählen.
Wo, also ob im Report oder im SQL ist mir mittlerweile ziemlich egal.

Habs im SQL Versucht mit der Gesamtzahl ohne Where oder sonst was

Code:

SELECT        FilteredIncident.incidentid, FilteredIncident.statecode, FilteredIncident.statuscode, FilteredIncident.ticketnumber, FilteredIncident.title,
                         FilteredIncident.new_companyid, FilteredIncident.casetypecode, FilteredIncident.casetypecodename, FilteredIncident.statecodename, Count(FilteredIncident.incidentid) as allIncidents
FROM            FilteredAccount INNER JOIN
                         FilteredIncident ON FilteredAccount.accountid = FilteredIncident.new_companyid
WHERE        (FilteredIncident.new_companyid = @accountid)

Wenn ich da Ok Clicke, bekomme ich folgenden Fehler:

Die FilteredIncident.incidentid-Spalte ist in der Auswahlliste ungültig, da sie nicht in einer Aggregatfunktion und nicht in der GROUP BY-Klausel enthalten ist.

Viele Grüße
Pascal

Zuletzt geändert von pallinio am 29. April 2011 15:14, insgesamt 1-mal geändert.

Hallo Pascal,

wenn du Count verwendest, muss das verwendete Feld in ein Group BY. So sollte der Befehl funktionieren:

Code:

SELECT        FilteredIncident.incidentid, FilteredIncident.statecode, FilteredIncident.statuscode, FilteredIncident.ticketnumber, FilteredIncident.title,
                         FilteredIncident.new_companyid, FilteredIncident.casetypecode, FilteredIncident.casetypecodename, FilteredIncident.statecodename, Count(FilteredIncident.incidentid) as allIncidents
FROM            FilteredAccount INNER JOIN
                         FilteredIncident ON FilteredAccount.accountid = FilteredIncident.new_companyid
WHERE        (FilteredIncident.new_companyid = @accountid)
GROUP BY FilteredIncident.incidentid

Hallo Michael, hat leider nicht funktioniert...

habs jetzt anders gelöst...

Code:

SELECT        COUNT(FilteredIncident_1.incidentid) AS allIncidents,
                             (SELECT        COUNT(incidentid) AS Expr1
                               FROM            FilteredIncident
                               WHERE        (statecode = 1) AND (new_companyid = @accountid)) AS closedIncidents,
                             (SELECT        COUNT(incidentid) AS Expr1
                               FROM            FilteredIncident AS FilteredIncident_9
                               WHERE        (statecode = 0) AND (new_companyid = @accountid)) AS activeIncidents,
                             (SELECT        COUNT(incidentid) AS Expr1
                               FROM            FilteredIncident AS FilteredIncident_8
                               WHERE        (statecode = 2) AND (new_companyid = @accountid)) AS canceledIncidents,
                             (SELECT        COUNT(DISTINCT customeridname) AS Expr1
                               FROM            FilteredIncident AS FilteredIncident_7
                               WHERE        (new_companyid = @accountid)) AS numberofContacts,
                             (SELECT        COUNT(incidentid) AS Expr1
                               FROM            FilteredIncident AS FilteredIncident_6
                               WHERE        (new_companyid = @accountid) AND (DATEDIFF(m, createdon, GETDATE()) <= @period)) AS allCasesInPeriod,
                             (SELECT        COUNT(incidentid) AS Expr1
                               FROM            FilteredIncident AS FilteredIncident_5
                               WHERE        (statecode = 1) AND (new_companyid = @accountid) AND (DATEDIFF(m, createdon, GETDATE()) <= @period)) AS closedCasesInPeriod,
                             (SELECT        COUNT(incidentid) AS Expr1
                               FROM            FilteredIncident AS FilteredIncident_4
                               WHERE        (statecode = 0) AND (new_companyid = @accountid) AND (DATEDIFF(m, createdon, GETDATE()) <= @period)) AS activeCasesInPeriod,
                             (SELECT        COUNT(incidentid) AS Expr1
                               FROM            FilteredIncident AS FilteredIncident_3
                               WHERE        (statecode = 2) AND (new_companyid = @accountid) AND (DATEDIFF(m, createdon, GETDATE()) <= @period)) AS canceledCasesInPeriod,
                             (SELECT        COUNT(DISTINCT customeridname) AS Expr1
                               FROM            FilteredIncident AS FilteredIncident_2
                               WHERE        (new_companyid = @accountid) AND (DATEDIFF(m, createdon, GETDATE()) <= @period)) AS numberofContactsInPeriod
FROM            FilteredAccount INNER JOIN
                         FilteredIncident AS FilteredIncident_1 ON FilteredAccount.accountid = FilteredIncident_1.new_companyid
WHERE        (FilteredIncident_1.new_companyid = @accountid)

Viele Grüße
Pascal

Hallo zusammen,
der Ansatz von Michael war schonmal richtig. Aber ich glaube du benutzt MS SQL da müssen dann alle Felder in das Group By, welche nicht eine Aggregatsfunktion sind.
SELECT FilteredIncident.incidentid, FilteredIncident.statecode, FilteredIncident.statuscode, FilteredIncident.ticketnumber, FilteredIncident.title,
FilteredIncident.new_companyid, FilteredIncident.casetypecode, FilteredIncident.casetypecodename, FilteredIncident.statecodename, Count(FilteredIncident.incidentid) as allIncidents
FROM FilteredAccount INNER JOIN
FilteredIncident ON FilteredAccount.accountid = FilteredIncident.new_companyid
WHERE (FilteredIncident.new_companyid = @accountid)
GROUP BY FilteredIncident.incidentid, FilteredIncident.statecode, FilteredIncident.statuscode, FilteredIncident.ticketnumber, FilteredIncident.title, FilteredIncident.new_companyid, FilteredIncident.casetypecode, FilteredIncident.casetypecodename, FilteredIncident.statecodename
So hätte es auch funktioniert.

Hallo,

so hatte ich es versucht.
Da wurden aber aus 240 Einträgen auf einmal nur noch 190.
Darum hab ich es dann anders gemacht.

Viele Grüße
Pascal