Count how many times a combination of values occur

[gitguto]

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.

[Vegar]

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;