CODEWITHSUNDEEP
sql-servert-sql
Mac
Mac

Reputation: 123

How would I count rows for distinct groups, ordered by date?

Sorry if the title is a little confusing. A little background - We have customers who belong to certain groups, and those customer can change between groups at any time. I'm trying to create a column that chronologically list which groups the customers joined in what order, based on the date they joined said group.

This is what the table looks like: Cust_Tbl

| Account | Group | Join_Date  |
|---------|-------|------------|
| 1000    | 21    | 2007-07-22 |
| 1000    | 26    | 2017-03-08 |
| 1002    | 21    | 2014-06-19 |
| 1002    | 23    | 2019-01-01 |
| 1003    | 26    | 2005-10-05 |
| 1004    | 21    | 2018-05-07 |

This is what I want it to look like: Cust_Tbl2

| Account | Group | Join_Date  | Group_Order |
|---------|-------|------------|-------------|
| 1000    | 21    | 2007-07-22 | 1           |
| 1000    | 26    | 2017-03-08 | 2           |
| 1002    | 21    | 2014-06-19 | 1           |
| 1002    | 23    | 2019-01-01 | 2           |
| 1003    | 26    | 2005-10-05 | 1           |
| 1004    | 21    | 2018-05-07 | 1           |

As you can see, the groups are enumerated in chronological order, split by account.

I've tried plenty of other methods - including some code I found elsewhere on SO -

Alter table to add Identity column based on Order By

I'm relatively new to this, so this wasn't entirely easy for me to understand (had issues with the identity property, but that isn't my main question)

The closest thing I've been able to come up with is a simple window function (which is also new to me). Obviously this doesn't work because it doesn't group by the account number. 

select 
    *
    , ROW_NUMBER() over(order by Join_Date) as num 
from 
    Cust_Tbl

So far, I'm at a stand still. Any help is greatly appreciated!

Edit: Updated query to match tables

Upvotes: 0

Views: 43

Answers (1)

Gen Wan
Gen Wan

Reputation: 2009

To get your expected result, you just need to add [partition by account]:

select 
    *
    , ROW_NUMBER() over(partition by account order by [group]) as num 
from 
    Cust_Tbl

You used [order by rate_str].

I do not know what the [rate_str] is. I think you can use order by [group]

But:

One thing you need to clarify is:

For example, I added the third row for account 1000:

| 1000    | 21    | 2007-07-22 |
| 1000    | 26    | 2017-03-08 |
| 1000    | 21    | 2017-02-08 |

Is your expected group order 1,2,3 or 1,2,2?

How about this?

| 1000    | 21    | 2007-07-22 |
| 1000    | 21    | 2017-03-08 |
| 1000    | 26    | 2017-02-08 |

Upvotes: 2

Related Questions