CODEWITHSUNDEEP
sqloracle-database
b3ck19
b3ck19

Reputation: 1

Select Count of Distinct rows and sum

I have a table that looks like this:

Item     |     Document
-----------------------
Book     |      BL
Book     |      CT
Book     |      OP
Pen      |      CT
Pen      |      BL
Mug      |      OP

I need to get the count of all unique items and the count of all documents in that table. For the example above, I'm looking for an output like this:

3        |     6

3 - Book, Pen, Mug
6 - total count of available documents

My SQL looks like this:

 SELECT ITEM , COUNT (*) AS DOC_COUNT
    FROM TABL1
  GROUP BY ITEM
  HAVING COUNT (*) > 0

But I'm only getting the list of all items with their document count. Can someone help me figure out how to solve this? Thanks!

Upvotes: 0

Views: 115

Answers (3)

Jacob
Jacob

Reputation: 14741

If your expected output is 

3        |     6

3 - Book, Pen, Mug
6 - total count of available documents

Kindly try the below query

SELECT TO_CHAR (COUNT (DISTINCT item)) AS item_count, 
       TO_CHAR (COUNT (document)) AS doc_count 
  FROM (SELECT item, document FROM table1) 
UNION ALL 
SELECT    COUNT (item) 
       || ' - ' 
       || LISTAGG (item, ',') WITHIN GROUP (ORDER BY item) 
          items, 
       NULL 
  FROM (SELECT DISTINCT item 
          FROM ( (SELECT item, document FROM table1))) 
UNION ALL 
SELECT COUNT (document) || ' - total count of available documents' document, 
       NULL 
  FROM (SELECT document 
          FROM ( (SELECT item, document FROM table1)))

You can use ROLLUP as well

  SELECT item, COUNT (item)
    FROM table1
GROUP BY ROLLUP (item)
ORDER BY item

Example

Upvotes: 0

Akanksha
Akanksha

Reputation: 101

Please try 

SELECT * FROM table1;
SELECT item ,
  MAX(ITEMS_)ITEM,
  MAX(DOC_)DOC,
  MAX(ro_num)TOTAL_ITEM
FROM
  (SELECT item,
    row_number() over (partition BY ITEM order by ITEM DESC)ITEMS_,
    row_number() over (partition BY document order by document DESC ) DOC_,
    MAX(rownum) over (partition BY 1)ro_num
  FROM table1
  )
GROUP BY item

Upvotes: 2

user5683823
user5683823

Reputation:

Elementary, my dear Watson. 

select count ( distinct item ) as item_count
     , count ( document )      as doc_count
from   tabl1
;

Upvotes: 4

Related Questions