Re: Count Distinct

Joseph, The mismatch is being introduced by your use of the "after group of company". The count() function is reporting the number of rows passed into your report. But, you're not printing a label for each row, you're only printing when the company name changes. Are you sure your labels are printing correctly?! I don't know your application, but it sounds like you might be missing labels. Anyway, here are my suggestions: Idea 1 --------- Fix your select to only return the rows you want to print. (I'm basing this guess on your partial? example so be careful!) SELECT id_number, last_name, company, first_name, address, city_state, zip_code, greeting, source, comment, did_number, ddonor_date, damount, dnoncashdescrip FROM labels, donor WHERE ddonor_date = (SELECT MAX(ddonor_date) FROM donor WHERE ddonor_date >= $startdate AND ddonor_date <= $enddate) AND did_number = id_number ORDER BY zip_code, last_name, company; In this example, you might even leave off the join of labels to donor. It doesn't appear that you're using any of the donor columns in you report. Idea 2 --------- If the distinct count isn't working, count the labels by hand. DEFINE cnt INTEGER FIRST PAGE HEADER LET cnt = 0 AFTER GROUP OF company LET cnt = cnt + 1 . . . ON LAST ROW PRINT "Total Labels Printed: ", cnt USING "<<<<<<&" If you have questions about this, feel free to write. Good Luck! --Craig On 1 May 1996 22:06:58 -0400, arcieri@pilot.njin.net (Joseph Arcieri) wrote: >I'm having trouble getting a count from a labels program. I have 2 tables >(labels, donor) the labels table has name, address, etc and the donor table >has dollar amounts, dates,etc. I can select and print out the labels ok but >I'm having trouble using the count function to get a total of the labels I >just printed. I get a number much larger than what was printed. I know the >number includes duplicate rows but haven't been successful using count distinct >or unique. > >Here's a portion of the program. I'd appreciate any push in the right >direction. Thanks, Joe. > > > >select id_number,last_name,company,first_name,address,city_state,zip_code, > greeting,source,comment > did_number,ddonor_date,damount,dnoncashdescrip > from labels,donor> >where ddonor_date >=$startdate and ddonor_date <=$enddate >and did_number=id_number > >order by zip_code,last_name,company >end > > >format > >after group of company >if greeting is not null then >print greeting clipped, 1 space, first_name clipped, 1 space, last_name >else >print first_name clipped, 1 space, last_name >if company is not null then >print company without trailing blanks >print address >print city_state clipped, 1 space, zip_code > > >if company is null then >skip 3 lines >else >skip 2 lines > >on last row > >print "Total Labels Printed: ",count > >end > > ====================================================================== Craig Kickel | Unix/C/Database Consultant ckickel@cyberhighway.net | <This space intentionally left blank> ======================================================================