You have to group by all the identifying fields. So you just need to
list out all the fields. Here's a simpler example:
SELECT p.id, p.name, count(c.*) AS carCount
FROM person p
LEFT OUTER JOIN car c ON p.id = c.personID
GROUP BY p.id, p.name
ORDER BY p.name, p.id
If you omitted the "p.name" in the GROUP BY clause, you'd get the
error you're seeing, because that field is in the SELECT clause, but
is not an aggregate field. In this case, it's not actually helping
the GROUP BY clause at all, because the id field will do finer
differentiation, but it has to be there.
MySQL, lifts this restriction, so you don't HAVE to list everything in
the GROUP BY clause if it's not needed for grouping, but that's mostly
a kludge because it didn't have subquery support until relatively
recently (past couple years).
The example query above would be better written with a correlated subquery:
SELECT id, name, (
SELECT COUNT(*)
FROM car
WHERE personID = person.id
) AS carCount
FROM person
ORDER BY name, id
In your case, it looks like you're returning multiple fields from your
related tables, so I don't think you want a GROUP BY clause at all,
but I could be mistaken. GROUP BY in SQL and the GROUP attribute of
CFOUTPUT do totally different things for totally different reasons.
cheers,
barneyb
On 12/6/05, Will Tomlinson <[EMAIL PROTECTED]> wrote:
> I need to learn how to GROUP in my db's. I'm sick of using the group
> attribute in a <cfoutput>. It always seems to make things harder for me!
>
> Ok, I have products, each product has SKUS. I run my query on the products
> and SKUS. So far so good. How do I group the dang thing in the SQL?
>
> This doesn't work:
>
> SELECT tblProducts.prodname, tblProducts.prodshortdescription,
> tblProducts.prodID, tblProducts.prodshowonweb, tblSKUS.SKU_prodID,
> tblSKUS.SKUprice, tblSKUS.SKUsaleprice, tblProductCategories_rel.catID_rel,
> tblProductCategories_rel.catID, tblProductCategories_rel.ProdID,
> tblImages_rel.prodID, tblImages_rel.imagetypeID,
> tblImages_rel.prodimagefilename
> FROM (tblProducts
> LEFT OUTER JOIN tblImages_rel ON tblProducts.prodID = tblImages_rel.prodID)
> INNER JOIN tblProductCategories_rel ON tblProducts.prodID =
> tblProductCategories_rel.ProdID
> INNER JOIN tblSKUS ON tblProducts.prodID = tblSKUS.SKU_prodID
> WHERE tblProductCategories_rel.catID = <cfqueryparam
> cfsqltype="cf_sql_integer" value="#ARGUMENTS.CID#">
> AND prodshowonweb = <cfqueryparam cfsqltype="cf_sql_integer" value="1">
> GROUP BY tblProducts.prodID
> ORDER BY tblProducts.prodname
>
> I get this error:
>
> 'tblProducts.prodname' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
>
> How can I group this by the product ID?
>
> Thanks much,
> Will
>
--
Barney Boisvert
[EMAIL PROTECTED]
360.319.6145
http://www.barneyb.com/
Got Gmail? I have 100 invites.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:226358
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
