No you are not - you are trying to aggregate the results from multiple rows - NOT return them .. and you don't want the objectID you want a "count of objectids that meet a certain test".
-mk -----Original Message----- From: George Abraham [mailto:[EMAIL PROTECTED] Sent: Thursday, July 07, 2005 3:59 PM To: CF-Talk Subject: Re: SQL count of rows from a union query Mark, I am trying to return multiple rows of similarly typed data, specifically one column named ObjectID. Only, instead of deriving the number of rows from a getResults.RecordCount, I want to do it in the SQL query itself. Your suggestion will not weed out the duplicate instances of ObjectID which a UNION would accomplish. Thanks though, it would have worked save for the fact that the database was not the best conceived. I am having the deuce of a time doing any reporting. George On 7/7/05, Mark A Kruger <[EMAIL PROTECTED]> wrote: > Try something like.... > > SELECT count(ObjectID) > + > (SELECT count(ObjectID) FROM table1 t1 INNER JOIN table3 t3 ON t1.thisID > = t3.thisID) > AS total > > FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID > > > the "UNION" operator is really for returning multiple rows of similarly > typed data from disparate tables. That's not what you are trying to do here. > > -mk > > Mark A. Kruger, CFG, MCSE > www.cfwebtools.com > www.necfug.com > http://mkruger.cfwebtools.com > > > > > -----Original Message----- > From: George Abraham [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 07, 2005 2:22 PM > To: CF-Talk > Subject: OT: SQL count of rows from a union query > > > Hi all, > This one seems to be simple, but it is not working at all. I want to > return a count of the rows from an T-SQL Query that involves a UNION > operation. > > SELECT count(*) > FROM ( > SELECT ObjectID > FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID > UNION > SELECT ObjectID > FROM table1 t1 INNER JOIN table3 t3 ON t1.thisID = t3.thisID > ) > > But SQL Server keeps saying that there is a problem near that last > closing parentheses. > > Any solutions? > > George > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211388 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
