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?Try this:

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:

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;|||Thanks for the help. That's exactly what I needed.

group by all except one

Hello,
i want to ask whether exist some possibility to make sql query grouping all fields except one field. Because is annoying when you must make select something like:

SELECT a,b,c,d,e,count(f) FROM x GROUP BY a,b,c,d,e

when easiest way is something like.

SELECT a,b,c,d,e,count(f) from x GROUP BY ALL except f.

Is something like 'except' operator possible? and in other databases?

Thank youNo.