0

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.

1
  • Edited my answer to help assist based on your above edit. Sep 16, 2022 at 13:23

1 Answer 1

3

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.

5
  • Thank you so much, please find my new tests in my first message I edited :)
    – divad
    Sep 14, 2022 at 15:28
  • Hi Gortonington, thank you so much for your time ! I feel I have a big lack of knowledge here, sorry for that. I thought I could call the id from 'a' or from 'contacts' as it would be the same value. I adapted and tested your last query. I added a "AND contact.id = "003xxxxx" to find a single contact and I have 1 line per product type. I guess it's coherent as we deleted the top 1 so we have all matching lines. How can I have only 1 line per contact, the one with the most countType for each contact ? I can't find a solution with 'distinct' nor 'Max' on CountType. Thank you again !
    – divad
    Sep 19, 2022 at 15:30
  • @divad - try doing TOP 1 WITH TIES after the SELECT in the top level query. I think this might work. Sep 19, 2022 at 16:11
  • Hi @Gortonington ! Thank you so much for the tips, never heard about this ! Query Studio doesn't like it but I tried with Automation Studio. I had to adapt it a bit but I'd like to make sure what I did make sense. Can you have a look to my initial post I just edited again ('Edit 2') ? Thank you !
    – divad
    Sep 20, 2022 at 12:34
  • Hi @Gortonington ! I put an Edit 3 in my initial message with new things into. I really hope you could have a look to it. Thanks a lot !
    – divad
    Sep 21, 2022 at 16:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .