The Convert function may do the trick
SELECT Title, CONVERT(CHAR(10), BDate, 101) AS formattedDate, ID
FROM YOURTABLE
GROUP BY CONVERT(CHAR(10), BDate, 101), Title, ID
David DiPietro
Systems Developer / Engineer
OSU College of Medicine Public Health
Voice (614) 292-5960
Fax (614)
i think im close, but it just keeps going on..
looping infinitely!
i just want it to look like this
Studio01
Paul A 222- Joe D 333- Jack G 232-3232
Mel B 222- Harry E 333- Merril H 232-3232
Pete C 222- Ben F 333- Sue I 232-3232
Studio02
Paul A 222- Joe D 333-
SELECT *
FROM Something
GROUP BY DateSerial(Year([TheDate]),Month([TheDate]),Day([TheDate]))
ORDER BY DateSerial(Year([TheDate]),Month([TheDate]),Day([TheDate]));
Regards,
Matthew Walker
/*
Cabbage Tree Creative Ltd
Christchurch - New Zealand
of the decimals.
-Original Message-
From: Matthew Walker [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 08, 2002 6:31 PM
To: CF-Talk
Subject: RE: Group By
SELECT *
FROM Something
GROUP BY DateSerial(Year([TheDate]),Month([TheDate]),Day([TheDate]))
ORDER BY DateSerial(Year([TheDate
Oracle has a function called ROUND() that works on dates. Your DB may have
something similar (don't know what is it), you may also be able to cast a
date as a string.
I want to run a SQL query that groups by a day for multiple months. Some of
the dates have times but I want those times
That doesn't work because I have a sub query in my main select that depends
on thedate
This is getting more complex isn't it :)
Neil
- Original Message -
From: Costas Piliotis [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 08, 2002 9:40 PM
Subject: RE: Group
Where is that function from? I get an error while trying that?
Neil
- Original Message -
From: Matthew Walker [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 08, 2002 9:30 PM
Subject: RE: Group By
SELECT *
FROM Something
GROUP BY DateSerial(Year([TheDate
-Talk
Subject: Re: Group By
Where is that function from? I get an error while trying that?
Neil
- Original Message -
From: Matthew Walker [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 08, 2002 9:30 PM
Subject: RE: Group By
SELECT *
FROM Something
GROUP
I have a subquery in my select that depends on the tablename.thedate
I can't figure this out!!
Neil
- Original Message -
From: Matthew Walker [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, April 08, 2002 11:34 PM
Subject: RE: Group By
Sorry that's just Access. I only
thanks Chuck all
but it is still not working the way i envision it.
any more advice on this would be great!
i know i know it, just cant get it!
i have tried nesting different
the Current Results:
Adkins , John (241) Edit Photo
Adkins , John 222-223- Edit Photo
Anderson ,
output, otherwise it won't work
at all.
Adam.
-Original Message-
From: Paul Ihrig [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 07, 2002 11:23 AM
To: CF-Talk
Subject: RE: Group in cfoutput query. brain fart
thanks Chuck all
but it is still not working the way i envision
I'm not quite sure exaclt what you want but if you want to display the
name only once, then all of the phone numbers that are associated with
it, then you need multiple grouping values:
CFOUTPUT QUERY=Employees GROUP=city
Office - Studio
#Employees.City# -
07, 2002 12:23 PM
To: CF-Talk
Subject: RE: Group in cfoutput query. brain fart
thanks Chuck all
but it is still not working the way i envision it.
any more advice on this would be great!
i know i know it, just cant get it!
i have tried nesting different
the Current Results:
Adkins , John
Need to use cfloop you can't nest cfoutputs.
Dave
-Original Message-
From: Matthew R. Small [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 07, 2002 12:54 PM
To: CF-Talk
Subject: RE: Group in cfoutput query. brain fart
I'm not quite sure exaclt what you want but if you want
843-333-
- Matt Small
-Original Message-
From: Bosky, Dave [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 07, 2002 1:28 PM
To: CF-Talk
Subject: RE: Group in cfoutput query. brain fart
Need to use cfloop you can't nest cfoutputs.
Dave
-Original Message-
From: Matthew R
My bad I didn't notice the group attribute in the second cfoutput.
Dave
-Original Message-
From: Matthew R. Small [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 07, 2002 2:02 PM
To: CF-Talk
Subject: RE: Group in cfoutput query. brain fart
Dave, you can absolutely nest cfoutputs
Mathew all thank you!
got me back on track!
thank you!
what i did below.
-paul
cfquery name=Employees datasource=RPH
SELECT tblEmployee.Lastname + ' ' + tblEmployee.Firstname as Fullname,
tblEmployee.EMPLOYEE_ID, tblEmployee.EmployeeID, tblEmployee.StudioID,
tblEmployee.Status,
In your query, order by employee ID. It will look like this:
cfquery name=Employees datasource=RPH
SELECT tblEmployee.FirstName, tblEmployee.LastName,
tblEmployee.EmployeeID,
tblEmployee.StudioID, tblEmployee.Status, tblStudio.StudioID,
tblStudio.OfficeID, tblStudio.StudioName,
Add an order by studioid. Anytime you do a group by in a cfoutput tag,
you must order by the same column in the query. I always thought that the
attribute should be order by - as it is, it's kinda confusing.
Depending on the many-to-one relationships here, you might need several
order bys and
This makes your query run slow, but it worked for me. Just replace
reportmonth with your select name.
CFQUERY DATASOURCE=#Application.DSN# NAME=updateguide
SELECT eventid, eventdate, longtitle
FROM whatson
Where cfif reportmonth is 1 OR reportmonth is 3 OR reportmonth is 5 OR
reportmonth is 7
I'm no Oracle expert, but this can do it (maybe there is a better way)
cfparam name=form.month default=01
cfset startdate = CreateDate(Year(Now()),form.month,1)
cfset enddate = DateAdd(m,1,startdate)
CFQUERY DATASOURCE=#Application.DSN# NAME=updateguide
SELECT eventid, eventdate, longtitle
FROM
WHERE month(TO_DATE(eventdate)) = #form.eventdate#
should be close. I don't have Oracle installed here currently.
-Original Message-
From: Ian Vaughan [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 17, 2002 10:29 AM
To: CF-Talk
Subject: Group queries in Coldfusion by date.
Hi
Hi Ian,
Here's an example of what you're trying to do. I tested this on a table of
mine, so the fields are a little different, but you'll get the idea.
!---query for all dates, grouped by month---
cfquery name=test password=#pword# username=#uname#
datasource=#dsn#
SELECT datecollected,
how about order by?
JoAnn A. Schlosser
Senior Consultant
Association Management Software
Grant Thornton LLP
Washington, D. C.
703.837.4428
-Original Message-
From: Koo Pai Lao [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 20, 2001 4:59 PM
To: CF-Talk
Subject: Group Query I
You need to sort your SELECT statement, right now data is coming back any
old way (well, probably how it is stored in the table). Add an ORDER BY
clause.
--- Ben
-Original Message-
From: Koo Pai Lao [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 20, 2001 4:59 PM
To: CF-Talk
Because I want to use grouping, as I will be grouping by different things
other than Id's later on
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: RE: Group Query I going Crazy!!!
Date: Thu, 20 Sep 2001 17:03:44 -0400
how about order by?
JoAnn
ok, for some reason it doesn't come out grouped as I want
it to!! anyone know why???
You don't need a GROUP attribute with your CFOUTPUT tag. Instead, you need
to add an ORDER BY prod_type_id, name clause to your SQL query.
The GROUP attribute allows you to build hierarchical data displays,
You don't need the groupby in the cfoutput
You need it in the cfquery, as long as that is what your data is going to
look like.
cfquery name=foo datasource=#application.dsn#
SELECT prod_type_id, id, show, price, name
FROM prod
GROUP BY prod_type_id, id, show, price, name
OH! now I remember. man i haven't used it for so long heh
From: Dave Watts [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: RE: Group Query I going Crazy!!!
Date: Thu, 20 Sep 2001 17:13:50 -0400
ok, for some reason it doesn't come out grouped as I
You don't need the groupby in the cfoutput
You need it in the cfquery, as long as that is what
your data is going to look like.
cfquery name=foo datasource=#application.dsn#
SELECT prod_type_id, id, show, price, name
FROM prod
GROUP BY prod_type_id, id, show, price,
CFQUERY DATASOURCE=#dsn# name=get_prod
SELECT name, price, show, prod_type_id, id
FROM prod
Order by prod_type_id
/cfquery
TABLE CELLSPACING=4 CELLPADDING=0 BORDER=0 bgcolor=#E7E7DA
cfoutput query=get_prod
TR
TD#Prod_type_id#--#name#nbsp;nbsp;nbsp;/tdTD#price#nbsp;nbsp;
nbsp;/td
/tr
/cfoutput
When you use aggregate functions in the SELECT clause to produce summary
information, you use the GROUP BY clause to divide the information into
distinct groups. Your database system uses any column or lost of columns
following the GROUP BY keywords as grouping columns. It is an optional
clause.
Here is an example
Let say you have an orders table that contains the UserID of the person who
has placed the order. You want to count the number of order by each user
and display them.
You could write a query like this:
SELECT UserID, count(OrderID) AS OrdersForUser
FROM Orders
GROUP BY
/CCFD :)
Application Development
http://www.schoollink.net/
-Original Message-
From: Nick McClure [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 24, 2001 10:22 AM
To: CF-Talk
Subject: Re: Group By..
Here is an example
Let say you have an orders table that contains the UserID of the person
.
John
- Original Message -
From: Bruce Sorge [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, August 24, 2001 9:58 AM
Subject: Re: Group By..
When you use aggregate functions in the SELECT clause to produce summary
information, you use the GROUP BY clause to divide
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
Users Group
Sr. Systems Analyst
NCCIM/CIC
[EMAIL PROTECTED]
(256) 842-8342
-Original Message-
From: Rice, John J [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 24, 2001 9:58 AM
To: CF-Talk
Subject: RE: Group By..
to add to the two other posts:
GROUP BY in the SQL statement is generally
, or
retain this message. Please notify us on +44 (0)207 387 8890.
-Original Message-
From: Rice, John J [mailto:[EMAIL PROTECTED]]
Sent: 24 August 2001 15:58
To: CF-Talk
Subject: RE: Group By..
to add to the two other posts:
GROUP BY in the SQL statement is generally used
to do
To expand a bit on the use of CFOUTPUT GROUP=...
This allows the use of an internal nested CFOUTPUT to display
items within a group.
For example, if you have a recordset with records describing your
CD collection. One field in each record may indicate whether the
CD is country, pop,
Yeah, sorry wrote that up wrong.
Tim Heald
ACP/CCFD :)
Application Development
http://www.schoollink.net/
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 24, 2001 2:35 PM
To: CF-Talk
Subject: Re: Group By..
To expand a bit on the use
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
when using the group attribute of the CFOUTPUT tag you can control output
like so:
NO GROUP
---
cfoutput query=foo
#name# #movie#br
/cfoutput
James Bond GoldFinger
James Bond Octopussy
James Bond Never Say Never
James Bond The
And the SQL Group By will truncate any Memo fields you had in Access to
255 characters..
(still bitter...)
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ:
/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
Subject: RE: GROUP BY gives an error?
you need to include all of the columns in the SELECT in the GROUP BY also.
e.g.
SELECT modelname, modeldescription, makename, year
FROM MODELS
WHERE rating = 'Very Compatible'
GROUP BY makename, modeldescription, modelname, year
-Original Message-
From
I think you've misunderstood GROUP BY What is your intent for the GROUP
BY ??
If you change GROUP BY to ORDER BY, you can then use the GROUP
attribute on CFOUTPUT (or CFLOOP) to group together records with one
field in common.
-Original Message-
From: Jeff Fongemie [mailto:[EMAIL
you need to include all of the columns in the SELECT in the GROUP BY also.
e.g.
SELECT modelname, modeldescription, makename, year
FROM MODELS
WHERE rating = 'Very Compatible'
GROUP BY makename, modeldescription, makename, year
-Original Message-
From: Jeff Fongemie [mailto:[EMAIL
A better question is what the heck are you trying to do. If you use a group
by clause in sql you either need to include each field you're selecting in
the group by clause or do some sort of aggregate function to them. If you
trying to group the query by makename, then just order by makename in
Make sure you do an ORDER BY Month in your SQL or your groupings will be
broken up.
Dave
- Original Message -
From: David Shadovitz [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Wednesday, May 02, 2001 12:35 AM
Subject: RE: group by month??
That should do it. Just wanted
That should do it. Just wanted to mention that you could also try
GROUP BY DATEPART(m,myDateField)
Make sure you do an ORDER BY Month in your SQL or your groupings will be
broken up.
One thing I would add to this is that if you're showing more than one year,
or you get to the end of
Try:
SELECT MONTH(myDateField) As myMonth FROM ... in your SQL statement. That
should return a column in your result set that just has the month portion of
the date.
Bob
-Original Message-
From: Jeff Fongemie [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 01, 2001 7:16 PM
To: CF-Talk
That should do it. Just wanted to mention that you could also try
GROUP BY DATEPART(m,myDateField)
-David
On Tuesday, May 01, 2001 5:05 PM, Bob Silverberg [SMTP:[EMAIL PROTECTED]]
wrote:
Try:
SELECT MONTH(myDateField) As myMonth FROM ... in your SQL statement. That
should return a
-
From: Ed Toon [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 26, 2000 12:47 AM
To: CF-Talk
Subject: RE: Group Discounts a possibility for Conference?
I go to conferences every other day that offer group discounts. I just went
to one today. We took five developers, paid about 700 bucks per
Point taken... though with more than a few grains of salt. ;)
-Original Message-
From: Ken Wilson [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 25, 2000 11:43 PM
To: CF-Talk
Subject: RE: Group Discounts a possibility for Conference?
Ahh, but I didn't say anything about fiscal
I've never heard of any group discounts, but since the question came up, and anyone
here has any knowledge of this... how about giving a group discount to all the members
of Fusioneers.com (250+)?
---mark
--
Mark Warrick
Phone:
00 2:14 PM
To: CF-Talk
Subject: RE: Group Discounts a possibility for Conference?
I've never heard of any group discounts, but since the question came up, and
anyone here has any knowledge of this... how about giving a group discount
to all the members of Fusioneers.com (250+)
PROTECTED]
Sent: Monday, September 25, 2000 4:27 PM
Subject: RE: Group Discounts a possibility for Conference?
Ok I just got a call back from Ed Sullivan at Allaire and he says "no
group
discounts".
Boy, 250+ people though, that sounds like a number deserving of a group
discount
I go to conferences every other day that offer group discounts. I just went
to one today. We took five developers, paid about 700 bucks per person,
after a $300 discount per head. This is definitely not a sign of fiscal
strength. I'd say it's quite the opposite, but that'd sound like I wanted to
I am selecting 8 fields (one is a count) and I do not want a group by
clause.
Kathy,
You have to have a GROUP BY if you are using COUNT(). It's an aggregate
function -
that means it's meaningless without a GROUP BY. What are you trying to do?
Nick
By their very nature aggregate functions require a group by. If you have no
group by, then the
entire recordset is the group and location_city would make no sense unless
your entire recordset has the same value
for location_city
- Original Message -
From: "Kathy Bergman" [EMAIL
Have a look at iMS from CoolFusion.com
- Original Message -
From: Dan Schueler [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, May 05, 2000 5:27 PM
Subject: group email application
Does anyone have a corporate workgroup email application available?
What I'm thinking about is
101 - 161 of 161 matches
Mail list logo
