Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone, I need to count how many times a combination of values occur in a table:
Sample of my data:
row(no) | Material | Center | Family | Type | Expected Value |
1 | A | X | NEW | 1 | 3 |
2 | A | X | NEW | 2 | 3 |
3 | A | X | NEW | 3 | 3 |
4 | A | Y | NEW | 1 | 2 |
5 | A | Y | NEW | 4 | 2 |
6 | A | Y | - | - | 2 |
7 | A | Z | NEW | 1 | 1 |
8 | A | Z | - | - | 1 |
In red is my expected output. I need to count how many times the combination of Material&Center occurs if Family and Type arent null.
In other words:
A&X occur 3 times (Rows 1,2,3)
A&Y occur 2 times (Rows 4,5) (I dont count Row 6 because there are null values in Family and Type)
A&Z occur 1 time (Row 7) (I dont count Row 8 because there are null values in Family and Type)
How can I calculate this?
Thank you for your help.
Try something like this
table:
load
row(no) ,Material ,Center ,Family ,Type
from data;
left join (table) Load
Material, Center, count(Material) as expected
Resident table
where not isnull(Family) and not isnull(Type)
Group by Material, Center;