GROUPING SETS in What's New (Database Engine)

Just as a usability issue, most of the topics mentioned in the "What's New" documents have a link to further information. The GROUPING SETS item under "What's New in CTP 1" at /s10de_0evalplan/html/8f625d5a-763c-4440-97b8-4b823a6e2439.htm should contain a link to the GROUPING SETS topic. Of course, the GROUPING SETS entry in the index currently points to /s10de_6tsql/html/c1050658-b19f-42ee-9a05-ecd6a73b896c.htm which is actually the topic GROUPING_ID (Transact-SQL).

I realize that "What's New in CTP 1" will go away but I assume these groups of topics will be combined into a grander "What's New in SQL Server 2008" section distributed with RTM Books Online.

Hi Aaron,

Thanks for the good suggestions.

Regards,

Gail

GROUPING SETS in What's New (Database Engine)

Just as a usability issue, most of the topics mentioned in the "What's New" documents have a link to further information. The GROUPING SETS item under "What's New in CTP 1" at /s10de_0evalplan/html/8f625d5a-763c-4440-97b8-4b823a6e2439.htm should contain a link to the GROUPING SETS topic. Of course, the GROUPING SETS entry in the index currently points to /s10de_6tsql/html/c1050658-b19f-42ee-9a05-ecd6a73b896c.htm which is actually the topic GROUPING_ID (Transact-SQL).

I realize that "What's New in CTP 1" will go away but I assume these groups of topics will be combined into a grander "What's New in SQL Server 2008" section distributed with RTM Books Online.

Hi Aaron,

Thanks for the good suggestions.

Regards,

Gail

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.