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

Question

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.

Please define "combine them." What should the output look like? Show the six counts on separate rows, with a description to identify them? Or show them in six columns, with descriptive column names? Or what? — , Jun 24 '16 at 17:17
By combining i mean that i want a single query that does the work of all these 6 queries. I want to display the count in 6 different columns with descriptive column names. Thank You — nemish nigam, Jun 25 '16 at 02:07

score 2 · Accepted Answer · answered Jun 24 '16 at 18:39

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;

Thanks MT0 ...you just solved a really big headache for me.. Thanks a lot — nemish nigam, Jun 25 '16 at 11:13
Hi MT0, Will it be possible to fetch this data in columns rather than rows ? — nemish nigam, Jun 29 '16 at 12:06
Take each of the queries and then concatenate them using `UNION ALL` something like this `SELECT 'Docs_per_month' AS name, count(dataid) AS frequency FROM dtree ... UNION ALL SELECT 'Total_No_Of_Users', COUNT(a.id) FROM Kauf a ... UNION ALL ...` — MT0, Jun 29 '16 at 12:13

score 0 · Answer 2 · answered Jun 24 '16 at 18:20

Use UNION ALL statement

Ex. select count (a.x) from a...where... UNION ALL select count (b.z) from b...where... UNION ALL select count (c.y) from c...where... etc.

Note: you must use UNION ALL, because if you use regular UNION, duplicate results will not be shown

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

2 Answers2