Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: Query Help for Newbie

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2004
    Posts
    3
    Thanks a lot to you both, that did it.

    Really appreciate it...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    both? heh

    the other guy apparently took a hike and deleted his posts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You're so good, he thought you were two people.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    Jun 2004
    Posts
    3
    Opps....lol....counted my own post....

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, rich, you didn't

    there were two other posts by some other guy in this thread, but he turned around and deleted them when he saw mine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •