June 11, 2002 at 10:07 am
I have this query that computes the sum of dispositions by Date. Although I receive an error - Column 'tblManualCallData.DateCreated' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
The 'DateCreated' column is originally of datetime datatype. And looks like this - 'mm/dd/yyyy hh:mm:ss'
I don't know why it asks me to group by 'DateCreated' when I use 'convert(varchar(10), DateCreated, 101)'.
The code looks like this:
Select (convert(varchar(10), DateCreated, 101)) as ReportDate, ManualCallDisposition as Disposition,
count(tblManualCallData.ManualCallDispositionID) as Total
FROM tblManualCallData inner join tblManualCallDispositions Disp on tblManualCallData.ManualCallDispositionID = Disp.ManualCallDispositionID
Where (convert(varchar(10), DateCreated, 101)) between convert(varchar(10), DateAdd(d, -7, GetDate()), 101) and convert(varchar(10), DateAdd(d, -1, GetDate()), 101)
and tblManualCallData.ManualCallTypeID =1
Group by (convert(varchar(10), DateCreated, 101)), ManualCallDisposition
Order by (convert(varchar(10), DateCreated, 101)), count(tblManualCallData.ManualCallDispositionID)
Compute sum(count(tblManualCallData.ManualCallDispositionID)) by (convert(varchar(10), DateCreated, 101))
When I add a 'DateCreated' to 'Group by' clause, I don't get the correct result, because instead of grouping by 'mm/dd/yy', it groups by 'mm/dd/yy hh:mm:ss'.
Does anybody know what's going on here?
Thanks,
Nat
June 12, 2002 at 7:10 am
Did you use the same "(convert(varchar(10), DateCreated, 101))" in the group by clause as well? This should work. Let me know if it doesn't.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 12, 2002 at 7:12 am
Oops my bad didn't read far enough down. Looks like you are already doing that. No ideas then because that always works here. Sorry.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply