what DB are you working with, some, like Oracle have a sum(foo) over (partition by goo).. that you could use in this example
On 1/5/07, Matt Williams <[EMAIL PROTECTED]> wrote: > In SQL, is there an easy way to get the counts of something and then > also the total of all those counts? For example, I'm getting the count > of times each user has logged in, but I also need the total number for > all users. > > In 2 queries, it would be like so. > --For each user... > Select userID, Count(userID) AS userCount > FROM userLogins > GROUP BY userID > > --For total > Select Count(userID) AS totalCount > FROM userLogins > > > One solution I just tried is a bit of a hack, but it works. I did the > first query as above. Then, > <cfset LoginCounts = ValueList(myQ.userCount, '+') /> > <cfset TotalLogins = evaluate(LoginCounts) /> > > Yes, it uses the dreaded evaluate, but this just may be a case where > it comes in handy. What do y'all think? > > -- > Matt Williams > "It's the question that drives us." > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:265880 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
