SQL query over partition with condition

Question

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!

select #tbl_catalogo.*, Sum(Existencia) OVER(PARTITION BY Parent_Item, Calidad_id) as Curva_Real I´m a beginner, I don´t know how to do it...Looked for solutions in posts, but can´t solve it...thanks for the help — Maximiliano Vazquez, Mar 29 '22 at 00:16
Sounds like you mean count of *unique values* rather than SUM. Also, *always* include your dbms and version in the question tags. — SOS, Mar 29 '22 at 00:27
Done!. SQL server 2019. yes...the unique values is what I need... — Maximiliano Vazquez, Mar 29 '22 at 01:28
I don't think window functions are the right tool for a unique values query. See the suggestion below. — SOS, Mar 29 '22 at 16:36

score 1 · Accepted Answer · answered Mar 29 '22 at 15:40

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

SQL query over partition with condition

1 Answers1