roughly:
SELECT company, department, employee, time_in, time_out
FROM table
ORDER BY company, department, employee
<cfoutput group="company">
#company#<br>
<cfoutput group="department">
#department#<br>
<cfoutput group="employee">
#employee#<br>
<cfoutput>
#time_in#<br>
#time_out#<br>
</cfoutput>
</cfoutput>
</cfoutput>
</cfoutput>
the bill in in the mail.
-Rice
-----Original Message-----
From: Terry Troxel [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 24, 2001 12:34 PM
To: CF-Talk
Subject: Re: Group By..
This is addressed to all those of you who have been replying to this thread.
Ok, I am liking the speed of this using the group in CF, but how would you
write it to handle more then one group as in:
table fields
company
department
employee
time_in
time_out
If you could show me/us the query to handle
this along with
the CFoutput structure to handle grouping by
company, department, employee
and then all the time in, time out entries for each employee
I think we could all learn by it and I for one would greatly
appreciate it.
Terry Troxel
----- Original Message -----
From: Rice, John J <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Sent: Friday, August 24, 2001 7:58 AM
Subject: RE: Group By..
>
> to add to the two other posts:
>
> GROUP BY in the SQL statement is generally used
> to do summary reporting.
>
> you can only SELECT the columns that you GROUP BY
> and any other "column" in the SELECT statement
> must be and aggregate function.
>
> GROUP in CFOUTPUT will do the grouping at the
> client level not the dbms level.
>
> So, an ORDER BY must be used for the column
> that you are going to GROUP in the CFOUTPUT.
>
> per Nicks example:
>
> /**/
> SELECT UserID, count(OrderID) AS OrdersForUser
> FROM Orders
> GROUP BY UserID
> /**/
>
> Take away the GROUP BY UserID and COUNT() function
> and put ORDER BY UserID and jsut OrderID
>
> You get:
> /**/
> SELECT UserID, OrderID
> FROM Orders
> ORDER BY UserID
> /**/
>
>
> Then in CF
>
> <CFOUTPUT QUERY="qNAME" GROUP="UserID">
> #UserID#<br>
> </CFOUTPUT>
>
> Would return UserID once per value of UserID.
>
> <CFOUTPUT QUERY="qNAME" GROUP="UserID">
> #UserID#<br>
> <CFOUTPUT>| #OrderID# |
> </CFOUTPUT>
> </CFOUTPUT>
>
> Would return UserID once per value of UserID.
> and each orderID for the user.
>
>
> So the GROUP attribute is useful to do things
> at the client level but it is probably best
> to do summary groupoing at the dbms level for
> perfomance reasons.
>
> To plug Ben Forta:
>
> He has a great little book on his site at
> http://forta.com called Teach Yourself SQL in 10 minutes.
>
> It really takes 5-15 hours.. (10 minutes is each little excercise)
>
> It is very good. He gives
> a download for access db's used to work through
> the book. However in the functions section of the book
> he shows SQL server and ORACLE function but not for Access
> so you have to find the equiv functions in Access
> yourself to try what the book teaches at that point.
>
> Best Regards
> -Rice
>
>
>
>
> -----Original Message-----
> From: Jeffry Houser [mailto:[EMAIL PROTECTED]]
> Sent: Friday, August 24, 2001 12:43 PM
> To: CF-Talk
> Subject: Group By..
>
>
>
> This is one thing that has always escaped my understanding, and I had
the
> sudden urge to ask.
>
> What is Group By. How is Group By used? What is it used for? ( both
in
> SQL Statements and the group attribute in the CFOUTPUT tag )
>
> Anyone want to take a minute and attempt to explain it to me?
>
>
> --
> Jeffry Houser | mailto:[EMAIL PROTECTED]
> AIM: Reboog711 | ICQ: 5246969 | Phone: 860-229-2781
> --
> I'm looking for a room-mate in the Hartford CT area, starting in August
> --
> Instant ColdFusion 5.0 | ISBN: 0-07-213238-8
> http://www.instantcoldfusion.com
> --
> DotComIt, LLC
> database driven web data using ColdFusion, Lotus Notes/Domino
> --
> Far Cry Fly, Alternative Folk Rock
> http://www.farcryfly.com | http://www.mp3.com/FarCryFly
> --
> Change is good
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
