Summary on one line

[jasonhuibers]

Results of query are presently like this:
Category Count Status
Books 20 Closed
Books 10 OPEN

I would like to have them on one line like this - where Created_Count= Closed + Open:

Category Closed_Count Created_Count
Books 20 30


SELECT B.Category,count(distinct A.ID) COUNT,CASE WHEN A.STATUS = 'C' THEN 'CLOSED'
ELSE 'OPEN'
END STATUS
FROM
Table1 A , Table2 B
WHERE
B.CATEGORY = A.CATEGORY
AND B.DES= 'Books'
AND TRUNC(A.DATETIME_STAMP) >= to_date('01-01-2011','DD-MM-YYYY')
group by B.DES,a.STATUS;

 

[SkipVought]

hi,

SELECT   B.Category,count(distinct A.ID) as Closed_Count, 0 as Created_Count
  
FROM                 
Table1 A ,                Table2 B  
WHERE                  
B.CATEGORY = A.CATEGORY            
 AND B.DES= 'Books'     
AND TRUNC(A.DATETIME_STAMP)    >= to_date('01-01-2011','DD-MM-YYYY')          
 group by B.DES,a.STATUS
  and A.STATUS = 'C'; 

union all

SELECT   B.Category,0,count(distinct A.ID) COUNT
  
FROM                 
Table1 A ,                Table2 B  
WHERE                  
B.CATEGORY = A.CATEGORY            
 AND B.DES= 'Books'     
AND TRUNC(A.DATETIME_STAMP)    >= to_date('01-01-2011','DD-MM-YYYY')          
 group by B.DES,a.STATUS
  and A.STATUS <> 'C';

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Try

SELECT Category, Sum(Abs(Status="Closed")*[Count]) As Closed_Count, Sum([Count]) As Created_Count
FROM Table1
GROUP BY Category;

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

hi,

select Category, Sum(CC) as Closed_Count, sum(OC) as Created_Count

From (
SELECT   B.Category,count(distinct A.ID) as CC, 0 as OC
  
FROM                 
Table1 A ,                Table2 B  
WHERE                  
B.CATEGORY = A.CATEGORY            
 AND B.DES= 'Books'     
AND TRUNC(A.DATETIME_STAMP)    >= to_date('01-01-2011','DD-MM-YYYY')   
  and A.STATUS = 'C'       
 group by B.DES,a.STATUS
  ; 

union all

SELECT   B.Category,0,count(distinct A.ID) COUNT
  
FROM                 
Table1 A ,                Table2 B  
WHERE                  
B.CATEGORY = A.CATEGORY            
 AND B.DES= 'Books'     
AND TRUNC(A.DATETIME_STAMP)    >= to_date('01-01-2011','DD-MM-YYYY')  
 and A.STATUS <> 'C'        
 group by B.DES,a.STATUS
  ; 
)
group by Category

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}