Joining 2 Count Query With Group By Clause

Question

I have 2 query
First one is:

SELECT UserId, COUNT(CustomerId) AS Total
  FROM (SELECT *
          FROM Customer
         WHERE JoinYear = 2016 
           AND JoinMonth = 1
           AND JoinWeek = 2 
           AND JoinDay = 1) x
GROUP BY UserId

Second one is:

SELECT UserId, COUNT(CustomerId) AS Joined
  FROM (SELECT *
          FROM Customer
         WHERE JoinYear = 2016 
           AND JoinMonth = 1
           AND JoinWeek = 2 
           AND JoinDay = 1
           AND JoinStatus = 2) x
GROUP BY UserId

Each of them will produce

(first query)                (second query)

UserId | Total              UserId | Total
--------------              --------------  
   1   |  10                   1   |   2
   2   |  15                   2   |   5

My question is how to join them to table like this?

Userid | Total | Joined
-----------------------
   1   |  10   |   2
   2   |  15   |   5

Answer 1

Your query is extremely complicated without reason.

Try this:

SELECT UserID, COUNT(*) Total, SUM(CASE WHEN JoinStatus = 2 THEN 1 END) Joined
FROM Customer
WHERE JoinYear = 2016 AND JoinMonth = 1 AND JoinWeek = 2 AND JoinDay = 1
GROUP BY UserID

Here's an SQLFiddle demonstrating this technique.

Whenever you find yourself with nested subqueries, ask yourself if that's really mandatory.

Answer 2

other oprion:

select  UserID, count(Total) as Total, count(Joind) as Joined
from    (
            select  UserId,COUNT(CustomerId) as Total, cast(0 as int) as Joined
            from    Customer
            where   JoinYear = 2016 and JoinMonth = 1 and JoinWeek = 2 and JoinDay = 1
            group by    UserID
            union all
            select  UserId,
                    cast(0 as int) as Total, count(CustomerId) as Joined Customer
            where   JoinYear = 2016 and JoinMonth = 1 and JoinWeek = 2 and JoinDay = 1  and JoinStatus = 2
            group by    UserId
        ) x
group by    x.UserID