Summary on one line 1

[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;

 

[Dagon]

What exactly is the problem that you are having? Also, what are your table definitions?

 

[SantaMufasa]

Another issue...The contents of CATEGORY and DES appear to be the same...it that correct?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SantaMufasa]

Here is a solution for you that uses virtual tables:

select x.category, Closed_count, Created_Count
  from (select a.category, count(*) closed_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 a.category
               ) x
      ,(select a.category, count(*) 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 a.category
               ) y
 where x.category = y.category
 order by 1
/

CATEGORY   CLOSED_COUNT CREATED_COUNT
---------- ------------ -------------
Books                20            30

1 row selected.

Let us know if this solves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Beilstwh]

SELECT
  B.Category,
sum(decode(a.status,'C',1,0)) closed,
sum(decode(a.status,'C',0,1)) open,
sum(1) 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.Category;

Bill
Lead Application Developer
New York State, USA

 

[SantaMufasa]

Excellent improvement, Bill. I shoulda thunka that myself. Hava

!

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[stefanhei]

The OP used count(distinct a.id). If the join returns duplicate ids a slightly modified version Santas approach - count(distinct id) instead of count(*) - would produce the desired result.