How can I use the “count” keyword in SOQL?

97    Asked by ConnorPeake in Salesforce , Asked on Apr 23, 2024

 I am currently developing a custom reports generation tool for a Salesforce CRM user. How can I use the “count” keyword in SOQL to retrieve the total number of opportunities closed won by each sales representative within a specific period? 

Answered by Deepa bhawana

 In the context of Salesforce, you can retrieve the total number of opportunities closed won by each sales representative within a specific period in Salesforce by using the SOQL, you would use the “COUNT” function along with the required filters and grouping. Here is an example given of how you can construct the SOQL queries and additional consideration:-

Here is an example given which includes a Python script using the Salesforce API for executing the SOQL queries and retrieving the count of closed won opportunities for each sales representative within a specific period.

Firstly, you would need to install the “simple salesforce” library if you haven’t already:-

  “pip install simple -salesforce”

Here is the Python script:-

From datetime import datetime

From simple_salesforce import Salesforce # Salesforce credentials
Username = ‘your_salesforce_username’
Password = ‘your_salesforce_password’
Security_token = ‘your_salesforce_security_token’
Domain = ‘login’ # or use your custom Salesforce domain
# Initialize Salesforce connection
Sf = Salesforce(username=username, password=password, security_token=security_token, domain=domain)
# Define start and end dates for the time period
Start_date = datetime(2024, 1, 1)
End_date = datetime(2024, 3, 31)
# Format dates in Salesforce SOQL format (YYYY-MM-DD)
Start_date_str = start_date.strftime(‘%Y-%m-%d’)
End_date_str = end_date.strftime(‘%Y-%m-%d’)
# SOQL query to retrieve count of closed-won opportunities by owner (sales representative)
Soql_query = f”””
SELECT Owner.Name, COUNT(Id)
FROM Opportunity
WHERE StageName = ‘Closed Won’
    AND CloseDate >= {start_date_str}
    AND CloseDate <= {end_date_str}
GROUP BY Owner.Name
ORDER BY Owner.Name ASC
“””
# Execute the SOQL query
Query_result = sf.query_all(soql_query)
# Process and display the results
If query_result[‘totalSize’] > 0:
    Print(“Opportunity Owner Closed-Won Count”)
    Print(“--------------------------------------------“)
    For record in query_result[‘records’]:
        Owner_name = record[‘Owner’][‘Name’]
        Closed_won_count = record[‘expr0’]
        Print(f”{owner_name} {closed_won_count}”)
Else:
    Print(“No closed-won opportunities found within the specified time period.”)

Here is the example given by using the Java programming language:-

Firstly, you would need to add the Salesforce java SDK dependencies to your project. If you are using maven then you would need to add the following dependencies to your “pom.xml” file:-


    com.force.api

    force-wsc

    49.0.0


Your Answer