sqloracle-databaseopentextlivelink

PL/SQL-How to implement multiple count statements in a single query when tables and conditions are all different


I'm working on Open Text Content Server Tool that uses PL/SQL Database. What I am trying to do is to fetch count data through 6 different queries all having different conditions and different tables too. I was trying to combine all these 6 count queries but no luck. Below are those 6 queries listed :

Documents Created in A Month:

select count (dataid) from Dtree where 
Createdate >= %1 and createdate <= %2 and subtype = 144

Total No of Users:

select count(a.id) from Kuaf a, kuaf b where 
a.deleted =0 and a.type =0 and b.id = a.groupid

Unique Users Logged in a Month(Count):

Select count (distinct (performerID))
from dauditnew where auditid=23 and auditdate >= %1 and auditdate <= %2

Users Created in a Month(Count):

Select Count(dataid) FROM DAUDITNEW where AUDITID = 1 
AND AUDITSTR LIKE 'Create' and subtype=142 AND 
auditdate >= %1 and auditdate <= %2

Users Deleted(Count):

SELECT count(a.userid) from dauditnew a WHERE
a.auditstr = 'Delete' AND 
a.AuditDate >= %1 AND 
a.AuditDate <= %2 AND 
a.UserID in (Select ID from KUAF where Deleted = 1 and Type=0)

Workflows Initiated:

Select count(*) from Wworkaudit WWA where WWA.workaudit_status=1 AND 
WWA.workaudit_date >= %1 and WWA.workaudit_date <= %2

Here %1,%2 denote user inputs. Since these 6 queries all have very different conditions, it seems a daunting task for me to combine them. Please help me out.

Thank You.


Solution

  • SELECT (
             select count (dataid)
             from   Dtree
             where  Createdate BETWEEN :start_date and :end_date
             and    subtype = 144
           ) AS Docs_Per_Month,
           (
             select count(a.id)
             from   Kuaf a INNER JOIN kuaf b ON (b.id = a.groupid)
             where  a.deleted = 0
             and    a.type    = 0
           ) AS Total_No_of_Users,
           (
             Select count( distinct performerID )
             from   dauditnew
             where  auditid = 23
             and    auditdate BETWEEN :start_date and :end_date
           ) AS Unique_Users_in_Month,
           (
             Select Count(dataid)
             FROM   DAUDITNEW
             where  AUDITID  = 1 
             AND    AUDITSTR = 'Create'
             and    subtype  = 142
             AND    auditdate BETWEEN :start_date and :end_date
           ) AS Users_Created_in_Month,
           (
             SELECT count(a.userid)
             from   dauditnew a
             WHERE  a.auditstr = 'Delete'
             AND    a.auditdate BETWEEN :start_date and :end_date
             AND    a.UserID in (Select ID from KUAF where Deleted = 1 and Type=0)
           ) AS Users_Deleted,
           (
             Select count(*)
             from   Wworkaudit
             where  workaudit_status = 1
             AND    workaudit_date BETWEEN :start_date and :end_date
           ) AS Workflows_Initiated
    FROM   DUAL;