rurth24
asked on
Another Query Question for ya.....
Hey I'm having a slight problem with this figuring out how to take my query to the next level..
What I'm trying to do is to display the sum of hours for each client related to a specific employee...
As of right now, my query sums up the whole month and displays it.
It should look like this on my page...
May
Company 1 12hrs
Company 2 20hrs
Company 2 40hrs
Total Company 1 Hours 12
Total Company 2 Hours 60
Etc.....
<CFQUERY name="mtotal"
datasource="#Request.MainD SN#">
SELECT SUM(HSPENT) AS monthsum FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER" value="#URL.EmployeesId#">
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
</CFQUERY>
<TABLE WIDTH="387" BORDER="0" CELLPADDING="3" CELLSPACING="3" CLASS="error">
<CFOUTPUT QUERY="mtotal"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="116">Total
<!--- #CompanyN# ---> (COMPANY NAME)
Hours </TD>
<TD WIDTH="250">#monthsum#</TD >
</TR>
<CFELSE>
<TR>
<TD WIDTH="116">Total Hours </TD>
</TR>
</CFIF> </CFOUTPUT>
</TABLE>
What I'm trying to do is to display the sum of hours for each client related to a specific employee...
As of right now, my query sums up the whole month and displays it.
It should look like this on my page...
May
Company 1 12hrs
Company 2 20hrs
Company 2 40hrs
Total Company 1 Hours 12
Total Company 2 Hours 60
Etc.....
<CFQUERY name="mtotal"
datasource="#Request.MainD
SELECT SUM(HSPENT) AS monthsum FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER"
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
</CFQUERY>
<TABLE WIDTH="387" BORDER="0" CELLPADDING="3" CELLSPACING="3" CLASS="error">
<CFOUTPUT QUERY="mtotal"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="116">Total
<!--- #CompanyN# ---> (COMPANY NAME)
Hours </TD>
<TD WIDTH="250">#monthsum#</TD
</TR>
<CFELSE>
<TR>
<TD WIDTH="116">Total Hours </TD>
</TR>
</CFIF> </CFOUTPUT>
</TABLE>
add a group by clause to your query....
group by clientId, employeesId, month(ddate)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Group actually worked...
If you notice in my table
<---------#CompanyN#----->
I'm trying to display this next to sums, but can't seem to get it to work. I keep getting an error....
Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'CompanyN' as part of an aggregate function.
May
Company 1 12hrs
Company 2 20hrs
Company 2 40hrs
Total (Company) 1 Hours 12
Total (Company) 2 Hours 60
<CFQUERY name="mtotal"
datasource="#Request.MainD SN#">
SELECT SUM(HSPENT) AS monthsum FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER" value="#URL.EmployeesId#">
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
GROUP BY Timesheets.ClientID
</CFQUERY>
If you notice in my table
<---------#CompanyN#----->
I'm trying to display this next to sums, but can't seem to get it to work. I keep getting an error....
Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'CompanyN' as part of an aggregate function.
May
Company 1 12hrs
Company 2 20hrs
Company 2 40hrs
Total (Company) 1 Hours 12
Total (Company) 2 Hours 60
<CFQUERY name="mtotal"
datasource="#Request.MainD
SELECT SUM(HSPENT) AS monthsum FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER"
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
GROUP BY Timesheets.ClientID
</CFQUERY>
ASKER
This is what I was trying to do.
<CFQUERY name="mtotal"
datasource="#Request.MainD SN#">
SELECT SUM(HSPENT) AS monthsum, Clients.CompanyN FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER" value="#URL.EmployeesId#">
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
GROUP BY Timesheets.ClientID
</CFQUERY>
<CFQUERY name="mtotal"
datasource="#Request.MainD
SELECT SUM(HSPENT) AS monthsum, Clients.CompanyN FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER"
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
GROUP BY Timesheets.ClientID
</CFQUERY>
make sure month(ddate) is returning a number and use the "IS" or "EQ" there
AND month(ddate) IS "#url.m#"
AND month(ddate) IS "#url.m#"
ASKER
I'm trying to get the Company Name(CompanyN) to display next to each sum...
The sums are working fine now... Just can't get the name to display...
Total (Company) 1 Hours 12
Total (Company) 2 Hours 60
The sums are working fine now... Just can't get the name to display...
Total (Company) 1 Hours 12
Total (Company) 2 Hours 60
I believe your error may come from the fact GROUP BY is looking for an expression in CompanyN, Group BY can be a real thorn in the side sometimes
SUM(HSPENT) AS monthsum alone would work, do you get the error when you add Clients.CompanyN?
ASKER
Just when I add Clients.CompanyN
ASKER
and if I take it out of the Query I get an error
Variable COMPANYN is undefined.
Variable COMPANYN is undefined.
Have you tried this??
<CFQUERY name="mtotal" datasource="#Request.MainD SN#">
SELECT SUM(HSPENT) AS monthsum, Clients.CompanyN FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID = <CFQUERYPARAM cfsqltype="CF_SQL_INTEGER" value="#URL.EmployeesId#">
<CFIF isdefined("url.m")>
and month(ddate) IS #url.m#
</CFIF>
</CFQUERY>
<TABLE WIDTH="387" BORDER="0" CELLPADDING="3" CELLSPACING="3" CLASS="error">
<CFOUTPUT QUERY="mtotal" group="ClientID"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="116">Total
<!--- #CompanyN# ---> (COMPANY NAME)
Hours </TD>
<TD WIDTH="250">#monthsum#</TD >
</TR>
<CFELSE>
<TR>
<TD WIDTH="116">Total Hours </TD>
</TR>
</CFIF> </CFOUTPUT>
</TABLE>
<CFQUERY name="mtotal" datasource="#Request.MainD
SELECT SUM(HSPENT) AS monthsum, Clients.CompanyN FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID = <CFQUERYPARAM cfsqltype="CF_SQL_INTEGER"
<CFIF isdefined("url.m")>
and month(ddate) IS #url.m#
</CFIF>
</CFQUERY>
<TABLE WIDTH="387" BORDER="0" CELLPADDING="3" CELLSPACING="3" CLASS="error">
<CFOUTPUT QUERY="mtotal" group="ClientID"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="116">Total
<!--- #CompanyN# ---> (COMPANY NAME)
Hours </TD>
<TD WIDTH="250">#monthsum#</TD
</TR>
<CFELSE>
<TR>
<TD WIDTH="116">Total Hours </TD>
</TR>
</CFIF> </CFOUTPUT>
</TABLE>
try this table instead
<TABLE WIDTH="387" BORDER="0" CELLPADDING="3" CELLSPACING="3" CLASS="error">
<CFOUTPUT QUERY="mtotal" group="ClientID"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="116">Total
COMPANY NAME: #CompanyN#
</TD>
<TD WIDTH="250">HOURS: #monthsum#</TD>
</TR>
<CFELSE>
<TR>
<TD WIDTH="116">Total Hours </TD>
</TR>
</CFIF> </CFOUTPUT>
</TABLE>
<TABLE WIDTH="387" BORDER="0" CELLPADDING="3" CELLSPACING="3" CLASS="error">
<CFOUTPUT QUERY="mtotal" group="ClientID"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="116">Total
COMPANY NAME: #CompanyN#
</TD>
<TD WIDTH="250">HOURS: #monthsum#</TD>
</TR>
<CFELSE>
<TR>
<TD WIDTH="116">Total Hours </TD>
</TR>
</CFIF> </CFOUTPUT>
</TABLE>
ASKER
Tried it and I got Errors.. 2nd from bottom
what's the error?
ASKER
Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'CompanyN' as part of an aggregate function.
I keep getting this error , once I add Clients.CompanyN into the query...
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'CompanyN' as part of an aggregate function.
I keep getting this error , once I add Clients.CompanyN into the query...
did you take GROUP BY Timesheets.ClientID out
and add group="ClientID" to your cfoutput query?
and add group="ClientID" to your cfoutput query?
ASKER
Hey Trailblazzyr55,
I took out the GROUP BY, but it was no difference.. I don't think it's that.
I got the same error.
I think it has to do with this line
SELECT SUM(HSPENT) AS monthsum, Clients.CompanyN FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID = <CFQUERYPARAM cfsqltype="CF_SQL_INTEGER" value="#URL.EmployeesId#">
Everytime I place Clients.CompanyN in the query that error comes up...
I took out the GROUP BY, but it was no difference.. I don't think it's that.
I got the same error.
I think it has to do with this line
SELECT SUM(HSPENT) AS monthsum, Clients.CompanyN FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID = <CFQUERYPARAM cfsqltype="CF_SQL_INTEGER"
Everytime I place Clients.CompanyN in the query that error comes up...
ASKER
I took out CompanyN and Replaced it with ClientID, since every client has it's corresponding ID number...
And took out Clients.CompanyN and Replaced it with Timesheets.ClientID
I can display the right number of the Client just not the name for some reason....There has to be something wrong in the query.
TABLE WIDTH="387" BORDER="0" CELLPADDING="3" CELLSPACING="3" CLASS="error">
<CFOUTPUT QUERY="mtotal" group="ClientID"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="116">Total
COMPANY NAME: #CompanyN# <----I changed This to -----> #ClientID#
</TD>
<TD WIDTH="250">HOURS: #monthsum#</TD>
</TR>
<CFELSE>
<TR>
<TD WIDTH="116">Total Hours </TD>
</TR>
</CFIF> </CFOUTPUT>
</TABLE>
And took out Clients.CompanyN and Replaced it with Timesheets.ClientID
I can display the right number of the Client just not the name for some reason....There has to be something wrong in the query.
TABLE WIDTH="387" BORDER="0" CELLPADDING="3" CELLSPACING="3" CLASS="error">
<CFOUTPUT QUERY="mtotal" group="ClientID"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="116">Total
COMPANY NAME: #CompanyN# <----I changed This to -----> #ClientID#
</TD>
<TD WIDTH="250">HOURS: #monthsum#</TD>
</TR>
<CFELSE>
<TR>
<TD WIDTH="116">Total Hours </TD>
</TR>
</CFIF> </CFOUTPUT>
</TABLE>
CompanyN shouldn't be giving you an error, especially if it's just a name of a company or names it's returning. Have you tried switching the order in which you call it? Not sure it'll make a big difference.
SELECT Clients.CompanyN,>>> SUM(HSPENT) AS monthsum<<<< also here, give HSPENT it's table name, ie: "clients.hspent"
And see if that works?
SELECT Clients.CompanyN,>>> SUM(HSPENT) AS monthsum<<<< also here, give HSPENT it's table name, ie: "clients.hspent"
And see if that works?
ASKER
AAAAARHHHGGGGGGGGGGGG
Damn, Damn, Damn.........LMAO
This is getting too funny..
I keep getting that same freakin error everytime I try and place CompanyN in the query...
Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'CompanyN' as part of an aggregate function.
The Error Occurred in C:\EmployeeTimeSheet2.cfm: line 60
58 : <TD width=246 height=39><A HREF="../index.html"><IMG HEIGHT=39 SRC="images/top04.gif" WIDTH=246 BORDER=0></A></TD>
59 : <TD vAlign=top align=left width=629 height=39><IMG height=39 SRC="images/corner_structu re.gif" width=143></TD>
60 : <TD WIDTH="285" HEIGHT="39" align=right class=mainTxtBd><CFOUTPUT> #session.f irstname# Is Currently Logged In<BR>
61 : <A HREF="client.cfm" CLASS="error">HOME</A> | <A href="logout.cfm" CLASS="error">LOGOUT</A> | <A HREF="employeeedit.cfm?Pro fileID=1" CLASS="error">MY PROFILE</A> </CFOUTPUT></TD>
62 : </TR>
<------------THIS IS WHAT I HAVE NOW---------------->
<CFQUERY name="mtotal"
datasource="#Request.MainD SN#">
SELECT Clients.CompanyN, Timesheets.ClientID, SUM(Timesheets.HSPENT) AS monthsum FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER" value="#URL.EmployeesId#">
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
GROUP BY Timesheets.ClientID
</CFQUERY>
<CFOUTPUT QUERY="mtotal"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="145">COMPANY NAME: #CompanyN# </TD>
<TD WIDTH="221">HOURS: #monthsum#</TD>
</TR>
<CFELSE>
<TR>
<TD WIDTH="145">COMPANY NAME: </TD>
<TD WIDTH="221">HOURS: </TD>
</TR>
</CFIF> </CFOUTPUT>
Damn, Damn, Damn.........LMAO
This is getting too funny..
I keep getting that same freakin error everytime I try and place CompanyN in the query...
Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'CompanyN' as part of an aggregate function.
The Error Occurred in C:\EmployeeTimeSheet2.cfm:
58 : <TD width=246 height=39><A HREF="../index.html"><IMG HEIGHT=39 SRC="images/top04.gif" WIDTH=246 BORDER=0></A></TD>
59 : <TD vAlign=top align=left width=629 height=39><IMG height=39 SRC="images/corner_structu
60 : <TD WIDTH="285" HEIGHT="39" align=right class=mainTxtBd><CFOUTPUT>
61 : <A HREF="client.cfm" CLASS="error">HOME</A> | <A href="logout.cfm" CLASS="error">LOGOUT</A> | <A HREF="employeeedit.cfm?Pro
62 : </TR>
<------------THIS IS WHAT I HAVE NOW---------------->
<CFQUERY name="mtotal"
datasource="#Request.MainD
SELECT Clients.CompanyN, Timesheets.ClientID, SUM(Timesheets.HSPENT) AS monthsum FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER"
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
GROUP BY Timesheets.ClientID
</CFQUERY>
<CFOUTPUT QUERY="mtotal"> <CFIF URL.M GTE 1>
<TR>
<TD WIDTH="145">COMPANY NAME: #CompanyN# </TD>
<TD WIDTH="221">HOURS: #monthsum#</TD>
</TR>
<CFELSE>
<TR>
<TD WIDTH="145">COMPANY NAME: </TD>
<TD WIDTH="221">HOURS: </TD>
</TR>
</CFIF> </CFOUTPUT>
try this... may help, not sure what's going on with CompanyN
SELECT Clients.[CompanyN], Timesheets.ClientID,.....
SELECT Clients.[CompanyN], Timesheets.ClientID,.....
ASKER
Nope...
ASKER
Anybody have any other ideas why this is happening? I can display the ClientID number but when I try to add the CompanyN field I get a weird Error...
CLIENTS.COMPANYN once I add this line I get an error....
You tried to execute a query that does not include the specified expression 'CompanyN' as part of an aggregate function.
<CFQUERY name="mtotal"
datasource="#Request.MainD SN#">
SELECT Clients.CompanyN, Timesheets.ClientID, SUM(Timesheets.HSPENT) AS monthsum FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER" value="#URL.EmployeesId#">
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
GROUP BY Timesheets.ClientID
</CFQUERY>
CLIENTS.COMPANYN once I add this line I get an error....
You tried to execute a query that does not include the specified expression 'CompanyN' as part of an aggregate function.
<CFQUERY name="mtotal"
datasource="#Request.MainD
SELECT Clients.CompanyN, Timesheets.ClientID, SUM(Timesheets.HSPENT) AS monthsum FROM Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER"
<CFIF isdefined("url.m")>
and month(ddate) = #url.m#
</CFIF>
GROUP BY Timesheets.ClientID
</CFQUERY>
ASKER
Still having the same problem.... ANYBODY??
ASKER
Nobody? :(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
IT WORKED... I could swear I tried that....... earlier today..
Thanks Rob.....
Thanks Rob.....
rurth24,
I'm glad you were able to get it working, thanks for the points!
Best Regards,
~trail
I'm glad you were able to get it working, thanks for the points!
Best Regards,
~trail
ASKER
No prob... I'm always looking for help so keep your eyes open.. Thanks for everything...
Yep I will, I'm always lookng to help, you're welcome
ASKER
Hey Trail Blazzy
take a look at this and let me know if you have any solutions.
https://www.experts-exchange.com/questions/21375676/Single-Quote-Issues.html
Nobody seems to know
take a look at this and let me know if you have any solutions.
https://www.experts-exchange.com/questions/21375676/Single-Quote-Issues.html
Nobody seems to know
ASKER
Or Rob..
I'm completely stuck..
I'm completely stuck..
ASKER
Everything is working fine, I just can't seem to get the query and cfoutput to break it down.