Have Geek Brain

Displaying multiple totals in one table from Oracle

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

but I also need to display total attachments / no attachments for each platform

SQL:

select the field, platform, counter (doc_id) attached to the drawing, where file_bin is not group zero by field, platform;

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

Is there a way to combine 3 values ​​in a view? eg Field, Platform, Total, Pinned, Unspecified [/ p>

0


a source to share


3 answers


Try the following:



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
+1


a source


thanks to Douglas Tosie's suggestion, I was able to use the case method instead.

select field, Platform, count (doc_id) as total, Amount (CASE WHEN file_bin is null THEN 1 WHEN file_bin is not null THEN 0 END) as attached Amount (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 fit!!

Thanks again Douglas

0


a source


I would use decoding instead of case, don't know which works 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
0


a source






More articles: