Displaying multiple totals in one table from Oracle

Question

I am trying to calculate the total rows in a table called DRAWING with the following query: Select field, platform, count(doc_id) as total from drawing group by field, platform;

but I also need to display the total of attachments/non-attachments for each platform

SQL:

select field,platform,count(doc_id) as attached from drawing where file_bin is not null group by field, platform;

select field,platform,count(doc_id) as non_attached from drawing where file_bin is null group by field, platform;

Is there a way to combine the 3 values into a view? e.g Field , Platform, Total, Attached, Non_attached

Answer 1

Try this:

select 
  field, 
  platform, 
  count(doc_id) as total,
  sum(iif(file_bin is null, 1, 0)) as attached,
  sum(iif(file_bin is not null, 1, 0)) as non_attached
from drawing 
where doc_id is not null 
group by field, platform

Answer 2

thanks to Douglas Tosi's suggestion, I managed to use the case method instead.

select field, platform, count(doc_id) as total, Sum(CASE WHEN file_bin is null THEN 1 WHEN file_bin is not null THEN 0 END) as attached, Sum(CASE WHEN file_bin is null THEN 0 WHEN file_bin is not null THEN 1 END) as non_attached from drawing where doc_id is not null group by field, platform

perfect!!

Thanks again Douglas

Answer 3

I would use decode instead of case, don't know what performs better (untested):

select field
,      platform
,      count(doc_id) as total
,      sum(decode(file_bin,null,1,0)) attached 
,      sum(decode(file_bin,null,0,1)) non_attached
from   drawing 
where  doc_id is not null 
group by field,platform