CODEWITHSUNDEEP
sqlsql-serverduplicates
Adhitya Sanusi
Adhitya Sanusi

Reputation: 119

Counting Using Duplicate Column

I have a table below. 

ClientID  | FileCount | LinkNumber | ActualFileCount | 
----------|-----------|------------|-----------------|
1         | 1         | 0          | 1               |
----------|-----------|------------|-----------------|
2         | 1         | 0          | 1               |
----------|-----------|------------|-----------------|
3         | 2         | 0          | 2               |
----------|-----------|------------|-----------------|
4         | 1         | 123        | 1               |
----------|-----------|------------|-----------------|
5         | 1         | 123        | 0               |
----------|-----------|------------|-----------------|
6         | 2         | 456        | 2               |
----------|-----------|------------|-----------------|
7         | 2         | 456        | 0               |
----------|-----------|------------|-----------------|
8         | 2         | 456        | 0               |
----------|-----------|------------|-----------------|
9         | 1         | 789        | 1               |
----------|-----------|------------|-----------------|
10        | 1         | 789        | 0               |

Basically if the LinkNumber is 0 then copy the FileCount column into ActualFileCount column. However, if the LinkNumber is not 0 then the ActualFileCount must be only a single value related to that LinkNumber.

For example ClientID 4 and 5, they are husband and wife, so we link them both. The system give them each FileCount 1. However, since they are link, there is only 1 ActualFileCount for both (only 1 'physical' file for both).

I use the syntax below I excel (using an example for ClientID 3 and ClientId 4)

If (LinkNumber = 0 then FileCount else
(If (LinkNumber for ClientID 4 = LinkNumber for ClientID 3 then 0 else FileCount)

When I translate it to SQL Server 2012, it doesn't work. 

select ClientID, file_count, LinkNumber,

If LinkNumber = 0 then file_count
else If LinkNumber + 1 = LinkNumber Then 0 else FileCount
end

as ActualFileCount

from TableA

order by LinkNumber asc

Thanks guys for your help.

Upvotes: 0

Views: 38

Answers (1)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

SELECT clientid,
       filecount,
       linknumber,
       CASE WHEN linknumber = 0 OR rnum = 1
            THEN filecount
            ELSE 0
        END actualfilecount
  FROM (SELECT *,
               ROW_NUMBER() OVER (PARTITION BY linknumber ORDER BY clientid) rnum
          FROM t_client
       ) t

Result

clientid    filecount   linknumber  actualfilecount
1           1           0           1
2           1           0           1
3           2           0           2
4           1           123         1
5           1           123         0
6           2           456         2
7           2           456         0
8           2           456         0
9           1           789         1
10          1           789         0

WITH result of ROW_NUMBER()

clientid    filecount   linknumber  actualfilecount rnum
1           1           0           1               1
2           1           0           1               2
3           2           0           2               3
4           1           123         1               1
5           1           123         0               2
6           2           456         2               1
7           2           456         0               2
8           2           456         0               3
9           1           789         1               1
10          1           789         0               2

Upvotes: 2

Related Questions