You may consider using sub queries. They may slow down the queries a bit, but it will work in the way you would like.
SELECT firstname, lastname, someothercolumn , (SELECT COUNT(*) FROM Orders WHERE orders.customerID = customers.customerID) as totalOrders , (SELECT COUNT(*) FROM Referrals WHERE referrals.customerID = customers.customerID) as referralTotal FROM Customers This is an example of using subselects, all you need is properly adjusted foreign and primary keys. Also microsoft sql server and oracle has several functions that'll help you build pivot tables. You can also use temp tables to build a matrix style report. A nice example is shown here: http://www.sqlteam.com/item.asp?ItemID=2955 ----- Original Message ----- From: "Eric Dawson" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, March 17, 2003 11:11 AM Subject: RE: Matrix problem > queryname.recordcount? > > e > > From: [EMAIL PROTECTED] > Reply-To: [EMAIL PROTECTED] > To: CF-Talk <[EMAIL PROTECTED]> > Subject: RE: Matrix problem > Date: Mon, 17 Mar 2003 09:52:27 -0600 > > You're right. Matrix must be thowing folks off. But I'm afraid you've lost > me, now. Let me try again. > > I'm working with a body of monthly 500 reports from which I need to distill > certain information. For the report output (which will take the form of a > matrix), assume column f is Brown_Eyes and assume row 5 is Big_Feet. > > I can easily imagine a query of the DB WHERE brown-eyes AND big_feet returns > a list of 25 of the 500 db records. But what I want to do is ouput the > COUNT, not a list of db records. > > Is this better? > > ------------------ > > Rick > > > I don't think I understood the question. And, given that there's been no > response in the last hour and a half, I suspect no one else did, either. > > To me, a matrix is simply an n-dimensional array where n > 1. In a > 2-dimensional matrix, column f will intersect row 5 exactly once. > > Your query approach suggests that you are working with a matrix of n > 2. > So you are looking for a count of (f, 5, x) is populated where x is the > third dimension. > > Am I anywhere near the mark? > > > -- Ben Doom > Programmer & General Lackey > Moonbow Software, Inc > > : -----Original Message----- > : From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > : Sent: Monday, March 17, 2003 8:13 AM > : To: CF-Talk > : Subject: Matrix problem > : > : > : I'm not very experienced at this, but I need to create a report > : that creates a matrix (the number of times where, say, column f > : intersects the row 5). > : > : I see how I can work the query (a kind of Where F AND 5 > : statement--other approaches appreciated). But I'm at a loss > : about how to output it so that it counts the total number instead > : of lists each incident. > : > : Is it done in the query or in the output? > : > : Rick > : > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
