Find the most represented value in a Data Extension

Question

I have 3 tables:

• Contacts (with SalesforceId as a key)
• OrderLines (with a contact SalesforceId, an OrderLineId and a ProductId)
• and Products (with a ProductId and a type of product).

I want to know, for each contact, what is the type of products he bought more (let's say : book, pen or computer). Because I'm having troubles doing this, I wanted to do something simple first: find the type of products the most represented in my Products table.

First step, if I do

SELECT COUNT(type__c) as countType, type__c
FROM Products
GROUP BY type__c

I have a result like

type__c	countType
Book	400
Computer	200
Pen	800

So I'd like to find a way to have only "Pen" shown as it's the most represented.

My idea was to add a simple 'MAX' but I have this message: Error saving the Query field.Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT MAX(COUNT(type__c)) as countType, type__c
FROM Products
GROUP BY type__c

Also, even if it were working, I'd need to have only the type__c as a result ( showing 'Pen'), not the whole count, so I'd have to use a subquery I guess.

I tried to find help here : https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-partition-by-another-column-in-mys but wasn't able to adapt the answer to my case as it seems I have one more level (the count).

Can you please try to help me with some tips ?

Thank you !

Edit 1 :

After Gortonington's answer, I've been able to adapt it ! I tried to go a bit further as I need to know the type of product mainly bought by my contacts but I'm stuck as I always have a TOP 1 of all my Contacts. I thought with a group by I could have a list by Contact but no.

Here is an adaptation of my code to keep it as easy as possible :)

SELECT TOP 1 
  contacts.id, a.type_c
FROM (
    SELECT TOP 100000000 COUNT(type__c) as countType, products.type__c, contacts.id
    FROM contacts
        join orderlines on orderlines.idContact = contacts.id 
        join products on orderlines.productId = products.id 
    GROUP BY type__c
    ORDER BY COUNT(type__c) DESC
) a 
GROUP BY contacts.id, a.type_c

It returns only 1 result. Here I'm not sure about how I can have 1 result by contacts.id , and only the TOP 1 for each of them.

Edit 2 :

After Gortonington's new answer, I adapted my query to be able to use the 'WITH TILES' keywords.

First, Automation Studio asked me to add an ORDER BY. I did it my way, do you think it's coherent (results seems to be coherent anyway) ?

Also, I had the opposite result I wanted. I mean I had the LESS represented value shown instead of the 'most represented value'. I tried to replace the DESC by ASC and my tests show me good results.

I would be very happy to know if what I did makes sense, and if I don't have good results only because I'm lucky :)

SELECT TOP 1 WITH TIES
  a.id, 
  a.type_c
FROM (
    SELECT TOP 100000000 COUNT(products.type__c) as countType, products.type__c, contacts.id
    FROM contacts
        join orderlines on orderlines.idContact = contacts.id 
        join products on orderlines.productId = products.id 
    GROUP BY products.type__c, contacts.id
    ORDER BY COUNT(products.type__c) ASC
) a 
ORDER BY a.id

(Extra question : I really don't understand why I had to do:

GROUP BY products.type__c, contacts.id

(in the last Edit you made). To me, it means that SQL creates a table without duplicates on the Type, then on the ContactId for each Type. It would have been easier to understand to me if it was first the ID, then the Type. As I want the result by Id. But it works the way to told me :p )

Edit 3 I had to put back DESC instead of ASC to have good results. More coherent but there is definitively something I can't understand with the results I had ! However I now have a new issue with this query. I might open a new post for this but I've the feeling the issue is linked with the 'TOP 1 WITH TIES' keywords.

To perform my tests, I actually added a condition "AND contact.id = '003xxxxxxx' so I could really check the results on 1 person. If I delete this condition (and let the Data Action to 'UPDATE'), I have an error "Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'ResultTable'. The duplicate key value is (003abcdefghijklmno)." The field SubscriberKey is the primary key of the table ResultTable. I confirm there is no duplicate for 003abcdefghijklmno on my 'contacts' table (checked with the Contact Builder>Data Extension search). However, this contact has several lines in 'orderlines' (but the primaryKey of the table orderlines is a different field).

If I run the query in Overwrite, I have only 1 line as a result, which has the SubscriberKey given in the error message.

So here I can see it's not only an issue linked to the Update mode. However, I tried to fix it with a solution given by Gortonington in another post. Here is the adaptation of the query with the ROW_NUMBER/PARTITION keywords :

SELECT c.*
    FROM (
    SELECT TOP 1 WITH TIES
      a.id, 
      a.type_c,
      ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY a.id) AS SubscriberKey,
    FROM (
        SELECT TOP 100000000 COUNT(products.type__c) as countType, products.type__c, contacts.id
        FROM contacts
            join orderlines on orderlines.idContact = contacts.id 
            join products on orderlines.productId = products.id 
        GROUP BY products.type__c, contacts.id
        ORDER BY COUNT(products.type__c) ASC
    ) a 
    ORDER BY a.id
) c
WHERE c.SubscriberKey = 1

With this query I have the same result than with the 'overwrite' mode (1 line, and with the same SubscriberKey than in the error message). The good thing is that I have it even in the Update mode.

Do you have any idea of what is the issue here ? If needed I can paste the real query I'm using (not an adaptation), and I'd modify the message when the solution is found.

Answer 1

You would just need to create a subquery to grab those values and then query that to get just the max count from that sub query.

See below for example:

SELECT TOP 1 
  a.type_c
FROM (
    SELECT TOP 100000000 COUNT(type__c) as countType, type__c
    FROM Products
    GROUP BY type__c
    ORDER BY COUNT(type__c) DESC
) a

Note in the subquery I have TOP 100000000 this is because SFMC has some hang ups on ORDER BY that requires a TOP or similar in order to include it. You can utilize PERCENT in Query Activity, but does not work in Query Studio. So I put this defined number to ensure it works in each place.

---

Edit based on further question

You would need to remove the TOP 1 from my example as that limits to just top response. Also you need to use a.id not contact.id as its pulling from the results of the subquery which contains the contact alias, so using that contact alias outside the subquery can cause errors or corrupt information.

SELECT  
  a.id, 
  a.type_c,
  a.countType
FROM (
    SELECT TOP 100000000 COUNT(products.type__c) as countType, products.type__c, contacts.id
    FROM contacts
        join orderlines on orderlines.idContact = contacts.id 
        join products on orderlines.productId = products.id 
    GROUP BY products.type__c, contacts.id
    ORDER BY COUNT(products.type__c) DESC
) a 

Above I believe is more along the lines of what you want.