The group by does not go in the Stored Procedure, it goes into the query
of a query. You also can not put the Count in the stored procedure
unless you have a group by, but if you put the group by in you will not
get the counts you want, that is why we are using the query of a query
to get those.
------cfCode to call procedure----
<cfstoredProc Datesource="YourDatabase" Procedure="countJobsLogged">
<cfprocparam dbvarname="startdate" cfsqltype="CF_SQL_datetime"
value="YourStartDate" type="In">
<cfprocparam dbvarname="stopdate" cfsqltype="CF_SQL_datetime"
value="YourStartDate" type="In">
<cfprocResult Name="qJobsLogged">
</cfstoredProc>
------Procedure------
------No Count here because you will not get the counts you are looking
for!!!!!
CREATE proc countJobsLogged @startdate datetime, @stopdate datetime
as
set nocount on
Select
JOBSCOUNT,DTLOGGED,jobPriority,jobNumber,convert(varchar(10),logdate,101
) logDate From Jobs Where logdatetime between @startdate and @stopdate
Order by convert(varchar(10),logdate,101)
------End Procedure----
------Now you can use query of query to get your count this way.---
------Since you did not count in the SP, you get them here!!!!!
<cfquery datasource="query" name="GetTotals">
Select Count(*) as MyCount
From qJobsLogged
Group By logDate
</cfquery>
Christian
-----Original Message-----
From: cf coder [mailto:[EMAIL PROTECTED]
Sent: Monday, March 14, 2005 10:49 AM
To: SQL
Subject: Re: sql - select query with count() and one or more columns
Hi Christian, thanks for replying to my query. Your sp throws an error.
It says there is no group by function. I modified it a bit by adding a
count(*). Any thoughts?
CREATE proc countJobsLogged @startdate datetime, @stopdate datetime
as
set nocount on
Select
count(*) AS
JOBSCOUNT,DTLOGGED,jobPriority,jobNumber,convert(varchar(10),logdate,101
) logDate
>From Jobs
Where logdatetime between @startdate and @stopdate
Order by convert(varchar(10),logdate,101)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2211
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
