I've used it.
Can you show us the SQL you are having a problem with?
--
Alan Rother
Macromedia Certified Advanced ColdFusion MX 7 Developer
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243360
Archives:
Here is a simple example using the Northwind database in SQL Server
The key to the GROUP BY clause is that is needs to be used in aggregate
functions
SELECT COUNT(P.ProductID) AS ItemTotal, C.CategoryName
FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID
GROUP BY
-Original Message-
From: Alan Rother [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 12:31 PM
To: CF-Talk
Subject: Re: Group By function for MS SQL Server Express 2005?
Here is a simple example using the Northwind database in SQL Server
The key to the GROUP BY clause is that is needs
: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 12:43 PM
To: CF-Talk
Subject: RE: Group By function for MS SQL Server Express 2005?
Ok...I had read a message that mentioned the aggregate function
requirement...but how would I write my example query to include an
(unneeded
By Book_Genre
Order By Book_Genre
Seems strange to require an unneeded aggregate function...
Rick
-Original Message-
From: Rick Faircloth [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 12:43 PM
To: CF-Talk
Subject: RE: Group By function for MS SQL Server Express 2005
Ok, I see what's going on. You want to out put your records in a group,
thats a CF issue, not a SQL issue.
What you need to do is to use ORDER BY to get your records in the correct
order then user the GROUP attribute of the CFOUTPUT tag
GROUP BY in SQL is for compiling related data and
Are you trying to get a count of all of the books by Genre? Or are you
trying to get a list of all of the genres?
--
Alan Rother
Macromedia Certified Advanced ColdFusion MX 7 Developer
~|
Message:
But why does it require that the count be calculated
if I don't need that information?
-Original Message-
From: Greg Morphis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 1:05 PM
To: CF-Talk
Subject: Re: Group By function for MS SQL Server Express 2005?
But it is needed
:05 PM
To: CF-Talk
Subject: Re: Group By function for MS SQL Server Express 2005?
But it is needed, that query is returning the count of the books per
book_genre
On 6/13/06, Rick Faircloth [EMAIL PROTECTED] wrote:
I finally did get Express to give me the results I wanted...
Here's
Ok...I had read a message that mentioned the aggregate
function requirement...but how would I write my example
query to include an (unneeded?) aggregate function?
(And why would there be an aggregate requirement anyway?)
Query (MySQL version):
Select * from books group by genre
The
Just a list of the genres to fill out a dropdown list...
SELECT DISTINCT Genre FROM Book
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago,
???
Rick
-Original Message-
From: Alan Rother [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 3:55 PM
To: CF-Talk
Subject: Re: Group By function for MS SQL Server Express 2005?
SELECT DISTINCT(Book_Genre) AS MyGenre
FROM Books
ORDER BY Book_Genre
This will accomplish what you
Yep...it was one of those duh moments...
Thanks for your help.
Rick
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 4:41 PM
To: CF-Talk
Subject: RE: Group By function for MS SQL Server Express 2005?
Just a list of the genres to fill out
Question: What's the benefit to using the alias? Is there a
benefit or is its usage just required?
Could it just be:
Select distinct Book_Genre
from Books
order by Book_Genre
You don't need an alias. SELECT DISTINCT fieldname FROM tablename will do.
Dave Watts, CTO, Fig Leaf
Not really, it's just a habit. I like to explicitlly control the name of the
resulting variable.
Dave is right, as usual, you can just leave it and it will retain it's given
column name.
--
Alan Rother
Macromedia Certified Advanced ColdFusion MX 7 Developer
15 matches
Mail list logo