Gary Kjos
garykjos at gmail.com
Thu Nov 3 16:18:00 CDT 2011
Well the original example had TWO A ee 101 records and I agree with Stuart that it would return 3 and not 2 for the simple group by with count in the same one. It needs a double query somehow, two group bys the first to eliminate the dups or a distinct to eliminate the dups lest you double count. Count will count ALL the records that it sees won't it? On Thu, Nov 3, 2011 at 4:08 PM, David McAfee <davidmcafee at gmail.com> wrote: > SELECT * FROM tblSomeTable > > PKID Uitv Sub Patient > ----------- ---- ---- ----------- > 1 A ee 101 > 2 A ee 102 > 3 A ff 201 > 4 A gg 301 > 5 B ee 201 > 6 B ee 301 > 7 B hh 501 > > (7 row(s) affected) > > > SELECT Uitv, Sub, COUNT(Patient) AS UniquePatient > FROM tblSomeTable > GROUP BY Uitv, Sub > ORDER BY Uitv, Sub > > Uitv Sub UniquePatient > ---- ---- ------------- > A ee 2 > A ff 1 > A gg 1 > B ee 2 > B hh 1 > > (5 row(s) affected) > > Maybe in Access/Jet, but in TSQL, the grouping takes care of that. > I do not see how Jet would be different. > > David > > > On Thu, Nov 3, 2011 at 1:53 PM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote: > >> That won't work, it will return 3 for A - ee. >> >> Gustav's solution is the way I would do it - use a select Distinct Query >> first to get rid of the >> duplicates. >> >> On 3 Nov 2011 at 9:49, David McAfee wrote: >> >> > SELECT Uitv, Sub, COUNT(Patient) AS UniquePatient >> > FROM tblSomeTable >> > GROUP BY Uitv, Sub >> > ORDER BY Uitv, Sub >> > >> > >> > On Thu, Nov 3, 2011 at 2:43 AM, <pedro at plex.nl> wrote: >> > >> > > Dear Group, >> > > >> > > how can i query the unique patients, from "Uitv" and "Sub". >> > > >> > > Data >> > > >> > > Uitv Sub Patient >> > > A ee 101 >> > > A ee 101 >> > > A ee 102 >> > > A ff 201 >> > > A gg 301 >> > > B ee 201 >> > > B ee 301 >> > > B hh 501 >> > > >> > > >> > > >> > > Result >> > > >> > > Uitv Sub Unique Patient >> > > A ee 2 >> > > A ff 1 >> > > A gg 1 >> > > B ee 2 >> > > B hh 1 >> > > >> > > >> > > Thanks >> > > >> > > Pedro Janssen >> > > >> > > -- >> > > AccessD mailing list >> > > AccessD at databaseadvisors.com >> > > http://databaseadvisors.com/mailman/listinfo/accessd >> > > Website: http://www.databaseadvisors.com >> > > >> > -- >> > AccessD mailing list >> > AccessD at databaseadvisors.com >> > http://databaseadvisors.com/mailman/listinfo/accessd >> > Website: http://www.databaseadvisors.com >> > >> >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Gary Kjos garykjos at gmail.com