Hi Matt, thanks for the quick response :) Matt Warden wrote: > I'm going to assume it is really set up like that (instead of >waiting for an answer, since I know you're in a time crunch). So, I'm >assuming your Phones table is like this: > >id confirmationcode longdistance dsl callerid >1 1 true true false >2 1 true false false > > Yes, it's set up similar to this. instead of true or false, i am storing the Plan name. I need to split them off of that too, but i can take care of that later. For all puposes, what we have above will work fine. >So, you want a summary of each person and how many lines have long >distance, dsl, and caller id. > > More or less. The data file I need to send has everything split out. For example, there are 3 DSL packages: DSL Extreme DSL Lite DSL Ultra I need to denote if one was ordered: 1 0 0 In my table, like you have above, I have a field called dsl, and the text value of the plan is stored there if ordered. >This is tough because if you use count(), which is what one woudl think >of first, it won't work, because you need count()s of records with >different filters applied. You would need three queries in this case, >one filtering on longdistance=true, another filtering on dsl=true, and >another filtering on callerid=true. > > exactly. and getting a group by on some 40 fields is not fun :P >Using a trick I picked up from the SQL God Himself, Rudy Limeback, what >(I think) you want can be accomplished. > >select m.fname, m.lname, > sum(case when longdistance=true then 1 else 0 end) countld, > sum(case when dsl=true then 1 else 0 end) countdsl, > sum(case when callerid=true then 1 else 0 end) countcid >from Main m, Phones p >where m.confirmationcode=p.confirmationcode >group by m.fname, m.lname > > I think what i want to group on is the confirmation code though... no? >your expanded recordset would look like this: > >joe guy 1 1 0 >joe guy 1 0 0 > >and grouped you will have: > >joe guy 2 1 0 > >which I think is what you want. > > let me play a little more with this. My code is very similar to what you have, only i'm joining with an explicit left outer join. i'll post back in a couple min :) thanks for the help.