GROUP BY problem

brewerd · #1 (**permalink**) 09-25-03, 11:13

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?

andrewst · #2 (**permalink**) 09-25-03, 12:51

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;

brewerd · #3 (**permalink**) 09-25-03, 14:19

Thanks for the help. That's exactly what I needed.

Internet Services

Web Development

Digital Marketing

Consumer Tech