CODEWITHSUNDEEP
sqlsql-server-2008
Mike Robson
Mike Robson

Reputation: 13

SQL (Subqueries)

Struggling to go the extra step with a SQL query I'd like to run.

I have a customer database with a Customer table with the date/time detail of when the customer joined and a transaction table with details of their transactions of the years

What I'd like to do is to Group by the Join Date (as Year) and count the number that joined in each year then in the next column I'd like to then count the number who have transacted in a specific year E.g. 2016 the current year. This way I can show customer retention over the years.

Both tables are linked by a customer URN, but I am struggling to get my head around the the most efficient way to show this. I can easily count and group the members by joined year and I can display the max dated transaction but I am struggling to bring the two together. I think I need to use sub queries and a left join but it's alluding me.

Example output column headers with data

Year_Joined = 2009

Joiner_Count = 10

Transact_in_2016 = 5

Where I am syntax-wise. I know this is no where near complete. As I need to group by DateJoined and then sub query the count of customers of have transacted in 2016? 

SELECT Customer.URNCustomer,
       MAX(YEAR(Customer.DateJoined)),
       MAX(YEAR(Tran.TranDate)) As Latest_Tran,
FROM Mydatabase.dbo.Customer   
LEFT JOIN Mydatabase.dbo.Tran
    ON Tran.URNCustomer = Customer.URNCustomer
GROUP BY Customer.URNCustomer
ORDER BY Customer.URNCustomer

Upvotes: 1

Views: 119

Answers (2)

JimmyB
JimmyB

Reputation: 12610

You may want to try something like this:

SELECT YEAR(Customer.DateJoined),
    COUNT( Customer.URNCustomer ),
    COUNT( DISTINCT Tran.URNCustomer ) AS NO_ACTIVE_IN_2016
FROM Mydatabase.dbo.Customer   
LEFT  Mydatabase.dbo.Tran
    ON Tran.URNCustomer = Customer.URNCustomer
    AND YEAR(Tran.TranDate) = 2016
GROUP BY YEAR(Customer.DateJoined)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

The best approach is to do the aggregation before doing the joins. You want to count two different things, so count them individually and them combine them.

The following uses full outer join. This handles the case where there are years with no new customers and years with no transactions:

select coalesce(c.yyyy, t.yyyy) as yyyy,
       coalesce(c.numcustomers, 0) as numcustomers,
       coalesce(t.numtransactions, 0) as numtransactions
from (select year(c.datejoined) as yyyy, count(*) as numcustomers
      from Mydatabase.dbo.Customer c
      group by year(c.datejoined)
     ) c full outer join
     (select year(t.trandate) as yyyy, count(*) as numtransactions
      from database.dbo.Tran t
      group by year(t.trandate)
     ) t
     on c.yyyy = t.yyyy;

Upvotes: 2

Related Questions