Link to home
Start Free TrialLog in
Avatar of rurth24
rurth24Flag for United States of America

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.MainDSN#">
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>
Avatar of rurth24
rurth24
Flag of United States of America image

ASKER

Oh URL.M is the Month (January (m)=1, Feburary(M)=2) etc..........

Everything is working fine, I just can't seem to get the query and cfoutput to break it down.
Avatar of rob_lorentz
rob_lorentz


add a group by clause to your query....

group by clientId, employeesId, month(ddate)
SOLUTION
Avatar of trailblazzyr55
trailblazzyr55

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rurth24

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.MainDSN#">
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>
Avatar of rurth24

ASKER

This is what I was trying to do.

<CFQUERY name="mtotal"
     datasource="#Request.MainDSN#">
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>
make sure month(ddate) is returning a number and use the "IS" or "EQ" there
AND month(ddate) IS "#url.m#"
Avatar of rurth24

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
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?

Avatar of rurth24

ASKER

Just when I add Clients.CompanyN
Avatar of rurth24

ASKER

and if I take it out of the Query I get an error

Variable COMPANYN is undefined.
Have you tried this??



<CFQUERY name="mtotal" datasource="#Request.MainDSN#">
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>
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>
Avatar of rurth24

ASKER

Tried it and I got Errors.. 2nd from bottom
what's the error?
Avatar of rurth24

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...
did you take GROUP BY Timesheets.ClientID out

and add group="ClientID" to your cfoutput query?
Avatar of rurth24

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...
Avatar of rurth24

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>
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?

Avatar of rurth24

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_structure.gif" width=143></TD>
60 :     <TD WIDTH="285" HEIGHT="39" align=right class=mainTxtBd><CFOUTPUT>#session.firstname# Is Currently Logged In<BR>
61 :         <A HREF="client.cfm" CLASS="error">HOME</A> |&nbsp;<A href="logout.cfm" CLASS="error">LOGOUT</A> | <A HREF="employeeedit.cfm?ProfileID=1" CLASS="error">MY PROFILE</A> </CFOUTPUT></TD>
62 :   </TR>
 
<------------THIS IS WHAT I HAVE NOW---------------->

<CFQUERY name="mtotal"
     datasource="#Request.MainDSN#">
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>
try this... may help, not sure what's going on with CompanyN

SELECT Clients.[CompanyN], Timesheets.ClientID,.....
Avatar of rurth24

ASKER

Nope...
Avatar of rurth24

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.MainDSN#">
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>
Avatar of rurth24

ASKER

Still having the same problem.... ANYBODY??

Avatar of rurth24

ASKER

Nobody? :(
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rurth24

ASKER

IT WORKED... I could swear I tried that....... earlier today..
Thanks Rob.....

rurth24,

I'm glad you were able to get it working, thanks for the points!

Best Regards,
~trail
Avatar of rurth24

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

Avatar of rurth24

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
Avatar of rurth24

ASKER

Or Rob..

I'm completely stuck..