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.