CODEWITHSUNDEEP
sqlselectsubquery
Jiminy Cricket
Jiminy Cricket

Reputation: 15

How Can I Combine These Two Statements in a sub select

Desired Output:

User -- Closed -- Open

Query 1: (Closed)
select AM1.SYSMODUSER, COUNT(AM1.SYSMODUSER)
from AuditLog AM1
where AM1.SYSMODTIME > '2015-05-01'
and AM1.SYSMODTIME < '2015-05-13'
and AM1.NUMBER like '%IM%'
and AM1.TYPE = 'Closed'
and (AM1.SYSMODUSER = 'login1'
or AM1.SYSMODUSER = 'login2')

Query 2: (Open)
select ASSIGNEE, count(record_id)
from List1
where "GROUP" = 'Records Compilation'
and RECORD_ID like '%IM%'
and ASSIGNEE is not null
group by ASSIGNEE

SYSMODUSER and ASSIGNEE share the same login names.

Also, if possible, I want it to show the logins even if they have a null or zero count. Currently, using either query, it only returns the actual count. If the user has no closed or open tasks, their name does not even appear in the result set. It would be optimal to see their name with a "0". I assume a case statement is needed for this.

Upvotes: 1

Views: 34

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Full outer join the queries to get all users even if they are only present in one of the two queries:

select 
  coalesce(open.userid, closed.userid) as userid, 
  coalesce(closed.cnt, 0) as closed, 
  coalesce(open.cnt, 0) as open
from
(
  select AM1.SYSMODUSER as userid, COUNT(AM1.SYSMODUSER) as cnt
  from AuditLog AM1
  where ...
  GROUP BY AM1.SYSMODUSER
) closed
full outer join
(
  select ASSIGNEE as userid, count(record_id) as cnt
  from List1
  where ...
  group by ASSIGNEE
) open on open.userid = closed.userid;

(Maybe open is a keyword. If you have problems, rename it.)

In case you want to show users not present in either query, you need a user table to select from:

select 
  user.id, 
  coalesce(closed.cnt, 0) as closed,
  coalesce(open.cnt, 0) as open
from user
left outer join (<query 1 here>) open on open.userid = user.id
left outer join (<query 2 here>) closed on closed.userid = user.id;

Upvotes: 2

Related Questions