Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Question Unanswered: GROUP BY problem

    Hi, I'm trying to write a query that gives me the total number of documents that exist in a table and also the total number of
    documents that were updated within the last 30 days. I need the new_documents field to contain 0 if no new documents were found. Here's what I have so far:

    SELECT b.doc_type,
    si_service_code_lookup.code_name,
    COUNT(b.doc_type) total_documents,
    COUN(b.doc_type) new_documents
    FROM (SELECT DISTINCT a.doc_type
    FROM (SELECT documents_by_esn_vu.doc_type
    FROM documents_by_esn_vu
    WHERE documents_by_esn_vu.doc_orig_date
    BETWEEN SYSDATE AND (SYSDATE - 30)) a ORDER BY a.doc-type) b,
    si_service_code_lookup
    WHERE b.doc_type = si_service_code_lookup.code
    AND si_service_code_lookup.code_type = 'Parts'
    GROUP BY b.doc_type,
    si_service_code_lookup.code_name;

    Any ideas on this?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: GROUP BY problem

    Try this:

    Code:
    SELECT d.doc_type,
           l.code_name,
           COUNT(*) total_documents,
           SUM(CASE WHEN d.doc_orig_date BETWEEN SYSDATE-30 AND SYSDATE THEN 1 ELSE 0 END) new_documents
    FROM   si_service_code_lookup l,
           documents_by_esn_vu d
    WHERE  d.doc_type = l.code
    AND    l.code_type = 'Parts'
    GROUP BY d.doc_type,
             l.code_name;
    Or if CASE doesn't work for your version of Oracle:

    Code:
    SELECT d.doc_type,
           l.code_name,
           COUNT(*) total_documents,
           SUM(DECODE(SIGN(d.doc_orig_date-(SYSDATE-30)),1,1,0)) new_documents
    FROM   si_service_code_lookup l,
           documents_by_esn_vu d
    WHERE  d.doc_type = l.code
    AND    l.code_type = 'Parts'
    GROUP BY d.doc_type,
             l.code_name;

  3. #3
    Join Date
    Jul 2003
    Posts
    15
    Thanks for the help. That's exactly what I needed.

Posting Permissions

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