CODEWITHSUNDEEP
sqlsql-servert-sql
Christopher Pettigrew
Christopher Pettigrew

Reputation: 211

Outer Join SQL on having query

I have the following query which (in my system) gets the total number of members who have more than 6 memberships....

select count(*) as MemberCount from (
   SELECT count(membership.memberid) as MembershipCount from Membership, Package
   WHERE membership.PackageId = Package.Id
   AND membership.DiscountPercentage != 100
   AND Package.PackageTypeId != 1
   AND membership.MembershipStateId != 5
   AND Membership.LocationId = 1
   group by memberid
   having count(membership.memberid) > 6
) NonTrialMemberships

What i need to do is left outer join the "having" part of it with a temporary table so i can get a listing of the number of members who have more than 1,2,3,4,5,6,7,8,9,10 memberships

having count(membership.memberid) > 6 <------(OUTER JOIN THIS)

I have created a temporary table to use as the join but not sure how to use it. I have also seen that using an in memory table may be more efficient than a temp table...

create table #Temp
(
Num int, 
)

DECLARE @i int = 0
DECLARE @total int = 10

WHILE @i < @total
BEGIN
    SET @i = @i + 1
    Insert Into #Temp values (@i)
END

select * from #Temp


select count(*) as MemberCount from (
    SELECT count(membership.memberid) as MembershipCount from Membership,     Package
    WHERE membership.PackageId = Package.Id
    AND membership.DiscountPercentage != 100
    AND Package.PackageTypeId != 1
    AND membership.MembershipStateId != 5
    AND Membership.LocationId = 1
    group by memberid
    having count(membership.memberid) > 6
) NonTrialMemberships




If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
    Drop Table #Temp
End

Any guidance would be appreciated.

Thank you.

Upvotes: 0

Views: 77

Answers (3)

Stan Shaw
Stan Shaw

Reputation: 3034

You want to select the counts, and get the count of each of those (sounds weird, but it's actually very simple):

SELECT MemberCount, Count(*)
FROM
(
       SELECT count(membership.memberid) as MemberCount
       FROM Membership, Package
       WHERE membership.PackageId = Package.Id
       AND membership.DiscountPercentage != 100
       AND Package.PackageTypeId != 1
       AND membership.MembershipStateId != 5
       AND Membership.LocationId = 1
       group by memberid
       having count(membership.memberid) > 6 
) t
group by MemberCount
order by MemberCount

You mentioned that you want to get the number of members who have 1, 2, 3, etc. memberships. If that is the case, you will want to remove the following line from the query:

having count(membership.memberid) > 6

Doing so will include a count of all counts (again, sounds weird), which seems to be your requirement. Please leave a comment if you would like clarification or if I misunderstood your question.

Upvotes: 0

Serg
Serg

Reputation: 22811

Try this, using your #temp.

select #temp.num, count(*)
from #temp
left join  
    (
    SELECT count(membership.memberid) as MembershipCount from Membership, Package
    WHERE membership.PackageId = Package.Id
    AND membership.DiscountPercentage != 100
    AND Package.PackageTypeId != 1
    AND membership.MembershipStateId != 5
    AND Membership.LocationId = 1
    group by memberid
    having count(membership.memberid) > 1
    ) ntm 
on ntm.MembershipCount > #temp.num 
group by #temp.num

definitly it will count users with 11 memberships in every >1, >2, ..., >10 group, as requested.

Upvotes: 1

Mike Miller
Mike Miller

Reputation: 16575

I would do it a different way

SELECT [MembershipCount], Count(1) FROM
(
        SELECT count(membership.memberid) as MembershipCount from Membership,     Package
        WHERE membership.PackageId = Package.Id
        AND membership.DiscountPercentage != 100
        AND Package.PackageTypeId != 1
        AND membership.MembershipStateId != 5
        AND Membership.LocationId = 1
        group by memberid
)
order by Count(1) Desc

Upvotes: 0

Related Questions