• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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>
0
rurth24
Asked:
rurth24
  • 19
  • 12
  • 2
2 Solutions
 
rurth24Author Commented:
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.
0
 
rob_lorentzCommented:

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

group by clientId, employeesId, month(ddate)
0
 
trailblazzyr55Commented:
try this not sure if group by will work in your query, but you can group your output...

You also have to make sure you select all the needed fields, or select all of the ' * '
SELECT clientId, employeesId, month(ddate)

<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>
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
rurth24Author Commented:
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>
0
 
rurth24Author Commented:
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>
0
 
trailblazzyr55Commented:
make sure month(ddate) is returning a number and use the "IS" or "EQ" there
AND month(ddate) IS "#url.m#"
0
 
rurth24Author Commented:
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
0
 
trailblazzyr55Commented:
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
0
 
trailblazzyr55Commented:
SUM(HSPENT) AS monthsum alone would work, do you get the error when you add Clients.CompanyN?

0
 
rurth24Author Commented:
Just when I add Clients.CompanyN
0
 
rurth24Author Commented:
and if I take it out of the Query I get an error

Variable COMPANYN is undefined.
0
 
trailblazzyr55Commented:
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>
0
 
trailblazzyr55Commented:
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>
0
 
rurth24Author Commented:
Tried it and I got Errors.. 2nd from bottom
0
 
trailblazzyr55Commented:
what's the error?
0
 
rurth24Author Commented:
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...
0
 
trailblazzyr55Commented:
did you take GROUP BY Timesheets.ClientID out

and add group="ClientID" to your cfoutput query?
0
 
rurth24Author Commented:
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...
0
 
rurth24Author Commented:
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>
0
 
trailblazzyr55Commented:
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?

0
 
rurth24Author Commented:
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>
0
 
trailblazzyr55Commented:
try this... may help, not sure what's going on with CompanyN

SELECT Clients.[CompanyN], Timesheets.ClientID,.....
0
 
rurth24Author Commented:
Nope...
0
 
rurth24Author Commented:
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>
0
 
rurth24Author Commented:
Still having the same problem.... ANYBODY??

0
 
rurth24Author Commented:
Nobody? :(
0
 
rob_lorentzCommented:
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, clients.CompanyN
0
 
rurth24Author Commented:
IT WORKED... I could swear I tried that....... earlier today..
Thanks Rob.....

0
 
trailblazzyr55Commented:
rurth24,

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

Best Regards,
~trail
0
 
rurth24Author Commented:
No prob... I'm always looking for help so keep your eyes open.. Thanks for everything...
0
 
trailblazzyr55Commented:
Yep I will, I'm always lookng to help, you're welcome

0
 
rurth24Author Commented:
Hey Trail Blazzy

take a look at this and let me know if you have any solutions.

http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21375676.html

Nobody seems to know
0
 
rurth24Author Commented:
Or Rob..

I'm completely stuck..
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 19
  • 12
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now