Question regarding Distinct Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with shipping data that includes the bill of lading number,
invoice number, weight, etc. The bill of lading number is a unique number,
but the invoice number is not. That is, there can be multiple bills of
lading for one invoice number.

I would like to run a query to determine how many bills of lading were for
each invoice, and the total weight, total $, etc, but I have been unable to
use the Distinct record and count function at the same time. What's the
shortcut?

Thanks.

Jay
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The JET SQL doesn't include the COUNT(DISTINCT <column>) function.

Without knowing the structure of your db, perhaps something like this
(use GROUP BY and other aggregate functions):

SELECT BillOfLading, Count(Invoice_Nr) As Invoices,
Sum(Weight) As TotalWeight, Sum(Amount) As TotalAmt
FROM tables
GROUP BY BillOfLading
ORDER BY BillOfLading

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkHo6YechKqOuFEgEQInhACgjjNRm27gis0foPmL9ljLUUFCdkIAoNVm
t2xiLoCXf5EFl1A6fU+qWR50
=ze7R
-----END PGP SIGNATURE-----
 
Distinct Records Queston <Distinct Records
I have a database with shipping data that includes the bill of lading number,
invoice number, weight, etc. The bill of lading number is a unique number,
but the invoice number is not. That is, there can be multiple bills of
lading for one invoice number.

I would like to run a query to determine how many bills of lading were for
each invoice, and the total weight, total $, etc, but I have been unable to
use the Distinct record and count function at the same time. What's the
shortcut?
Click to expand...


You don't want to use DISTINCT for aggregatng data. Use the
GROUP BY clause instead.

SELECT InvoiceNum,
Count(BillsOfLading) As LCount,
Sum(Weight) As TotalWeight,
Sum(Amount) As TotalAmount
FROM thetable
GROUP BY InvoiceNum
 
Back
Top