Why do you have the where clause in there. You don't need it. What you probably want is this: SELECT COUNT(o.bookid) AS TheCount, b.booktitle FROM orders o, books b Where o.bookid=b.bookid GROUP BY b.booktitle ORDER BY COUNT(bookid) DESC
----- Original Message ----- From: "Robert Orlini" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, September 19, 2003 12:46 PM Subject: select count > I have a shopping cart with a orders table. Each book has a bookid and its qty in this table. > > I would like to generate a list of all, the books and there corresponding qty. If a book is listed twice, it would not display twice, but show the qty as two. I'm trying the Select Count syntax, but am a bit stuck. > > This is what I have so far (I know it won't work completely): > > <cfquery name="totalbooks" datasource="printprices"> > SELECT COUNT(bookid) AS TheCount > FROM orders > Where bookid=bookid > ORDER BY COUNT(bookid) DESC > </cfquery> > > Thanks. > > Robert Orlini > HW Wilson > 718-588-8400 x2656 > τΏτ > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm?link=i:4:137717 Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
