'SQL query over partition with condition
I have a table like this, in SQL Server 2019:
| ITEM_ID | Parent_Item | Talle | Existencia |
|---|---|---|---|
| 909296 | 280647 | 4 | 1 |
| 909296 | 280647 | 4 | 1 |
| 909296 | 280647 | 4 | 1 |
| 909297 | 280647 | 5 | 1 |
| 909297 | 280647 | 5 | 1 |
| 909297 | 280647 | 5 | 1 |
| 909297 | 280647 | 5 | 1 |
| 909298 | 280647 | 6 | 1 |
| 909298 | 280647 | 6 | 1 |
| 909298 | 280647 | 6 | 1 |
| 909299 | 280647 | 7 | 1 |
| 909299 | 280647 | 7 | 1 |
| 909300 | 280647 | 8 | 1 |
| 909301 | 280647 | 9 | 1 |
| 909293 | 280647 | 11 | 1 |
| 909294 | 280647 | 12 | 1 |
| 909292 | 280647 | 10 | 1 |
| 1226447 | 280647 | 13 | 0 |
And I need a new column named 'Real Exist.' with the total sum of different values in 'Talle' Column, when 'Existencia' = 1. In this example (Parent_item = 280647) result should be 9 , because of values 4,5,6,7,8,9,11, 12, 10 in 'Talle' Column :
| ITEM_ID | Parent_Item | Talle | Existencia | Real Exist. |
|---|---|---|---|---|
| 909296 | 280647 | 4 | 1 | 9 |
| 909296 | 280647 | 4 | 1 | 9 |
| 909296 | 280647 | 4 | 1 | 9 |
| 909297 | 280647 | 5 | 1 | 9 |
| 909297 | 280647 | 5 | 1 | 9 |
| 909297 | 280647 | 5 | 1 | 9 |
| 909297 | 280647 | 5 | 1 | 9 |
| 909298 | 280647 | 6 | 1 | 9 |
| 909298 | 280647 | 6 | 1 | 9 |
| 909298 | 280647 | 6 | 1 | 9 |
| 909299 | 280647 | 7 | 1 | 9 |
| 909299 | 280647 | 7 | 1 | 9 |
| 909300 | 280647 | 8 | 1 | 9 |
| 909301 | 280647 | 9 | 1 | 9 |
| 909293 | 280647 | 11 | 1 | 9 |
| 909294 | 280647 | 12 | 1 | 9 |
| 909292 | 280647 | 10 | 1 | 9 |
| 1226447 | 280647 | 13 | 0 | 9 |
How could it be done?. Thanks in advance!
Solution 1:[1]
One way is using a derived query to count the number of unique values per Parent_Item
SELECT yt.Item_Id
, yt.Parent_Item
, yt.Talle
, yt.Existencia
, cnt.NumOfTalle AS [Real Exist]
FROM YourTable yt
INNER JOIN (
SELECT Parent_Item, COUNT(DISTINCT Talle) AS NumOfTalle
FROM YourTable
WHERE Existencia = 1
GROUP BY Parent_Item
)
cnt ON cnt.Parent_Item = yt.Parent_Item
Results:
Item_Id | Parent_Item | Talle | Existencia | Real Exist ------: | ----------: | ----: | ---------: | ---------: 909296 | 280647 | 4 | 1 | 9 909296 | 280647 | 4 | 1 | 9 909296 | 280647 | 4 | 1 | 9 909297 | 280647 | 5 | 1 | 9 909297 | 280647 | 5 | 1 | 9 909297 | 280647 | 5 | 1 | 9 909297 | 280647 | 5 | 1 | 9 909298 | 280647 | 6 | 1 | 9 909298 | 280647 | 6 | 1 | 9 909298 | 280647 | 6 | 1 | 9 909299 | 280647 | 7 | 1 | 9 909299 | 280647 | 7 | 1 | 9 909300 | 280647 | 8 | 1 | 9 909301 | 280647 | 9 | 1 | 9 909293 | 280647 | 11 | 1 | 9 909294 | 280647 | 12 | 1 | 9 909292 | 280647 | 10 | 1 | 9 1226447 | 280647 | 13 | 0 | 9
db<>fiddle here
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | SOS |