Developer forum

Counting SKUs which is unique product numbers.

Hi Nicolai,

Ok so basicly: SELECT ProductNumber, ProductVariantId FROM EcomProducts GROUP BY ProductNumber, ProductVariantId; ?

We have a customer that has a pretty messed up variant setup, and it appears that they've exceeded their Max product limit so I'm trying to figure out if it's because of the messed up variants or if it's legit.

Best regards,
Arnór

Hi Arnor

This is the actual code:

public static int NumberOfUniqueVariantCombinations()
        {
            var commandBuilder = new CommandBuilder();
            commandBuilder.Add("SELECT COUNT(*) as SkuCount FROM ");
            commandBuilder.Add("(");
            commandBuilder.Add("SELECT COUNT([ProductID]) as LanguageCount, [ProductID], [ProductVariantID] FROM EcomProducts");
            commandBuilder.Add("group by productid,[ProductVariantID]");
            commandBuilder.Add(") combinations");
            return Converter.ToInt32(Database.ExecuteScalar(commandBuilder));
        }

Great, muchos gracias.

One more question then I'll scurry back to my cave:

Are we subtracting the amount of "head products" in a variant set from this amount before actually checking if the customer exceeded the limit?

F.e. a product with 4 variants has 5 rows in the EcomProducts table, the first one is just a definition for the product set but we don't keep any stocks for that row. Not that it would make any difference in the case of this customer, but it could for someone else, f.e. if I filter out the head products in this db the difference is around 4600 Skus.

Best regards,
Arnór

A master with 4 variants count as 5 because they have different productid/variantid combinations.

Yes that's my point :) 

I was just wondering whether or not it would make sense to exclude the master row from the count since it's the other 4 variant rows that represent the physical products.