Query Help for Newbie

Rich Petersen · #1 (**permalink**) 06-14-04, 06:47

Hello all,

I have two tables (among others) that track the amount of documents scanned in a batch for a scanning application.

The table StatsBatch will have one record for each batch of documents, and there is an associated record in the table StatsBatchModule that tracks number of documents, pages, etc..

When a person scans, they select what Batchclass they want to use, lets call it BatchClassA, BatchClassB, BatchClassC.

I want to total the number of batches, then total the documents, and pages for each Batchclass, not individual batches. So if I scan 3 batches of BatchClassA, and the first one as 2 docs of a total of 5 pages, the second one has 4 docs with a total of 7 pages, and the third has 17 docs, with 1000 pages, then the return should show:

BatchClassA 3 23 1012

BatchClassA(name of batch) 3 (number of batches), 23 (number of total docs) and finally 1012 (number of pages).

Here is what I have so far:

Select distinct A.BatchClass, count(A.batchclass) as Batches, sum(B.DocumentsCreated) as Document, sum(B.PagesScanned) as Pages
From StatsBatch A, StatsBatchModule B
where A.externalbatchid = B.externalbatchid
Group by A.BatchClass
Order by A.BatchClass Asc

Everything works fine, except I am getting an invalid number of batches per batchclass. I figure the "Count(A.BatchClass) is not right, but I am not sure how to reconfigure the query to get the accurate count of how many of each batchclass there is.

Any help would be appreciated.

Thanks,
Rich

r937 · #2 (**permalink**) 06-14-04, 07:45

Code:

select A.BatchClass
     , count(distinct A.externalbatchid) as Batches
     , sum(B.DocumentsCreated) as Document
     , sum(B.PagesScanned) as Pages
  from StatsBatch A
inner
  join StatsBatchModule B
    on A.externalbatchid = B.externalbatchid
group 
    by A.BatchClass
order 
    by A.BatchClass asc