Search code examples
sqlsoql

Query on subquery containing JOINS in Salesforce SQL (SOQL)

SELECT 
    Email_address, COUNT(Order_date)
FROM 
    (SELECT 
         cust.Email_address, COUNT(ol.Variant_name), ord.Order_date
     FROM 
         DMW_Order_Line_v3 ol
     JOIN 
         DMW_Order_v3 ord ON ol.Unique_transaction_identifier = ord.Unique_transaction_identifier
                          AND ol.Brand_country = ord.Brand_country
     JOIN 
         DMW_Customer_v3 cust ON ord.Email_address = cust.Email_address
                              AND ord.Brand_country = cust.Brand_country
     WHERE 
         ord.Brand_country = 'kiehls-emea_CZ'
         AND cust.Address_country = 'CZ'
         AND cust.Optin_email != 'False'
         AND ol.Line_status = 'SHIPPED'
         AND ol.Variant_name = 'Sample'
     GROUP BY 
         cust.Email_address, ord.Order_date
     HAVING 
         COUNT(ol.Variant_name) >= 4)
GROUP BY 
    Email_address

Please, forgive me that I'm posting the whole body of the code. But it might be helpful somehow, who knows. As you can see it is a query on subquery containing joins. I'm using Salesforce SQL. When I run the code, I get this error:

Error saving the query field. Incorrect syntax near the keyword 'GROUP'.

What am I doing wrong? Besides being a noob ;-)

Solution

  • You don't need the count column in the subquery:

    SELECT Email_address, COUNT(*)
FROM (SELECT cust.Email_address, ord.Order_date
      FROM DMW_Order_Line_v3 ol JOIN
           DMW_Order_v3 ord
           ON ol.Unique_transaction_identifier = ord.Unique_transaction_identifier AND
              ol.Brand_country = ord.Brand_country JOIN
           DMW_Customer_v3 cust
           ON ord.Email_address = cust.Email_address AND
              ord.Brand_country = cust.Brand_country
      WHERE ord.Brand_country = 'kiehls-emea_CZ' AND
            cust.Address_country = 'CZ' AND
            cust.Optin_email <> 'False' AND
            ol.Line_status = 'SHIPPED' AND
            ol.Variant_name = 'Sample'
      GROUP BY  cust.Email_address, ord.Order_date
      HAVING COUNT(ol.Variant_name) >= 4
     ) e
GROUP BY Email_Address