CODEWITHSUNDEEP
sql
DarkW1nter
DarkW1nter

Reputation: 2851

Combine 3 UNIONed queries into one

I have the following which I would like to do without UNIONs so that the string split is only happening once.

I would also like the results to be in one line per MemberId showing all 3 counts rather than 3 rows.

SELECT MemberKey, 'login' as countType, count(MemberKey) as total FROM [dbo].[CA_MembersAudit]
WHERE isSuccess = 1 and MemberKey IN (SELECT value FROM STRING_SPLIT( @userList, ','))
Group By MemberKey
UNION


SELECT MemberId as MemberKey, 'articles' as countType, count(MemberId) as total FROM [dbo].[CA_Activities]
WHERE StateId = 'Opened' and  MemberId IN (SELECT value FROM STRING_SPLIT( @userList, ','))
Group By MemberId
UNION


SELECT MemberId as MemberKey,'assessments' as countType, count(MemberId) as total FROM [dbo].[CA_Activities]
WHERE PercentageComplete is not null AND  MemberId IN (SELECT value FROM STRING_SPLIT( @userList, ','))
Group By MemberId
UNION

Can anyone suggest how I should amend the queries into one to be able to do this?

Upvotes: 0

Views: 49

Answers (4)

Esperento57
Esperento57

Reputation: 17472

other solution :

SELECT f1.value as ID, isnull(f2.TotalOpened, 0) as TotalOpened, isnull(f2.TotalPercentageComplete, 0) as TotalPercentageComplete, isnull(f3.TotalSuccess, 0) as TotalSuccess
FROM STRING_SPLIT( @userList, ',') f1

outer apply
(
select sum(case when f1.StateId = 'Opened' then 1 else 0 end) as TotalOpened,
sum(case when f1.PercentageComplete is not null then 1 else 0 end) as TotalPercentageComplete
FROM [dbo].[CA_Activities] f1
where (f1.StateId = 'Opened' or f1.PercentageComplete is not null) and f1.MemberId=f1.value
) f2

outer apply
(
SELECT count(*) as TotalSuccess FROM [dbo].[CA_MembersAudit] f1 WHERE f1.isSuccess = 1 and f1.MemberKey=f1.value
) f3

Upvotes: 1

Esperento57
Esperento57

Reputation: 17472

try this :

With MemberList as (
SELECT value as ID FROM STRING_SPLIT( @userList, ',')
),

Activities as (
select f1.MemberId, sum(case when f1.StateId = 'Opened' then 1 else 0 end) as TotalOpened,
sum(case when f1.PercentageComplete is not null then 1 else 0 end) as TotalPercentageComplete
FROM [dbo].[CA_Activities] f1 inner join MemberList f2 on f1.MemberId=f2.ID
where f1.StateId = 'Opened' or f1.PercentageComplete is not null
group by f1.MemberId
),

MemberAudit as (
SELECT f1.MemberKey, count(*) as TotalSuccess 
FROM [dbo].[CA_MembersAudit] f1 inner join MemberList f2 on f1.MemberKey=f2.ID
WHERE f1.isSuccess = 1
Group By f1.MemberKey
)

select f1.*, isnull(f2.TotalOpened, 0) as TotalOpened, isnull(f2.TotalPercentageComplete, 0) as TotalPercentageComplete, isnull(f3.TotalSuccess, 0) as TotalSuccess
from MemberList f1
left outer join Activities f2 on f1.ID=f2.MemberId
left outer join MemberAudit f3 on f1.ID=f3.MemberKey

Upvotes: 1

Jerrad
Jerrad

Reputation: 5290

You could use a subquery for each total:

select m.MemberKey,
(select count(*) from CA_MembersAudit ma where m.MemberKey = ma.MemberKey and ma.isSuccess = 1) as 'login_total',
(select count(*) from CA_Activities a where m.MemberKey = a.MemberId and a.stateId = 'Opened') as 'articles_total',
(select count(*) from CA_Activities a where m.MemberKey = a.MemberId and a.PercentageComplete is not null) as 'assessments_total'
from (select value as MemberKey from STRING_SPLIT('1,2,3,4', ',')) m

If your tables have a primary key, you could also do something like this:

select m.MemberKey, 
count(distinct ma.Id) 'login_total',
count(distinct a1.Id) 'articles_total',
count(distinct a2.Id) 'assessments_total'
from (select value as MemberKey from STRING_SPLIT('1,2,3,4', ',')) m
left outer join CA_MembersAudit ma on m.MemberKey = ma.MemberKey and ma.isSuccess = 1
left outer join CA_Activities a1 on m.MemberKey = a1.MemberId and a1.stateId = 'Opened'
left outer join CA_Activities a2 on m.MemberKey = a2.MemberId and a2.PercentageComplete is not null
group by m.MemberKey

Upvotes: 1

Eric Brandt
Eric Brandt

Reputation: 8101

I believe you can use a CTE and then JOIN to each of the UNION participants.

WITH MemberList AS (
  SELECT value AS Member
        FROM STRING_SPLIT(@userList, ',')
)

SELECT 
  MemberKey
    ,'login' AS countType
    ,count(MemberKey) AS total
FROM [dbo].[CA_MembersAudit]
JOIN MemberList
  ON MemberList.Member = CA_MembersAudit.MemberKey
WHERE isSuccess = 1
GROUP BY MemberKey

UNION

SELECT 
  MemberId AS MemberKey
    ,'articles' AS countType
    ,count(MemberId) AS total
FROM [dbo].[CA_Activities]
JOIN MemberList
  ON MemberList.Member = CA_Activities.MemberId
WHERE StateId = 'Opened'
GROUP BY MemberId

UNION

SELECT 
  MemberId AS MemberKey
    ,'assessments' AS countType
    ,count(MemberId) AS total
FROM [dbo].[CA_Activities]
JOIN MemberList
  ON MemberList.Member = CA_Activities.MemberId
WHERE PercentageComplete IS NOT NULL
GROUP BY MemberId;

Upvotes: 1

Related Questions