[AccessD] SQL count distinct problem

SELECT W.vendor,
      (SELECT count(C) As Checks
       FROM (SELECT V.vendor,
                    V.checkno AS C
             FROM VendorChecks V
             GROUP BY V.vendor, V.checkno)
       WHERE V.vendor=W.vendor) AS NumOfChecks,
       Sum(W.amt) AS SumOfamt
FROM VendorChecks AS W
GROUP BY W.vendor;

Slightly more complicated, but another way of doing the query.

Scott Marcus

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Tuesday, March 07, 2006 5:26 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] SQL count distinct problem

That will certainly work but I was kind of hoping I was missing
something
simple (Like the distinct keyword) that would allow me to do it in one
query.
Jim Hale

-----Original Message-----
From: Heenan, Lambert [mailto:Lambert.Heenan at aig.com]
Sent: Tuesday, March 07, 2006 4:12 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] SQL count distinct problem


You'll need a query that looks like this...

SELECT DISTINCT Table1.Vendor, Table1.CHKNO FROM Table1;

Then you can use that query as the source of your other query to do the
grouping and counting.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Tuesday, March 07, 2006 4:53 PM
To: 'Accessd (E-mail)
Subject: [AccessD] SQL count distinct problem


Assume a table with vendor, inv amt and check number. There may be
multiple
records for the vendor with the same check number. If I want to
determine
the total amt paid to a vendor and the number of checks written to him a
simple groupby query will not do since the count function will count the
number of occurrences not number of distinct checks.
COUNT(DISTINCT(CHKNO)
works in a pass through query on the AS400 but not in Jet SQL. What is
the
equivalent Jet SQL to determine the number of distinct checks written
per
vendor? TIA Jim Hale

***********************************************************************
The information transmitted is intended solely for the individual or
entity
to which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of or
taking action in reliance upon this information by persons or entities
other
than the intended recipient is prohibited. If you have received this
email
in error please contact the sender and delete the material from any
computer. As a recipient of this email, you are responsible for
screening
its contents and the contents of any attachments for the presence of
viruses. No liability is accepted for any damages caused by any virus
transmitted by this email.
-- 
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

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
 





NOTICE:  This electronic mail transmission is for the use of the named individual or entity to which it is directed and may contain information that is privileged or confidential.  If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of any information contained herein is prohibited.  If you have received this electronic mail transmission in error, delete it from your system without copying or forwarding it, and notify the sender of the error by replying via email or calling TSS Technologies at (513) 772-7000, so that our address record can be corrected.  Any information included in this email is provided on an “as is” and “where as” basis, and TSS Technologies makes no representations or warranties of any kind with respect to the completeness or accuracy of the information contained in this email.