Group by Date Queries

SQL++
1

I am wondering if there is a simple way to create a query which will get me the sale by a sales person grouped by month ?
For example i would like to run a query that will show me the total of sales by salesperson for each month for the year 2019 for example the below gets me all of 2019 but how can i group it by month ?

SELECT Record.Agents.ListingAgent.ListAgentFirstName || " " || Record.Agents.ListingAgent.ListAgentLastName AS Agent,
       COUNT(*) as NbrofTransactions,
       SUM(TONUMBER(Record.Prices.ClosePrice)) as TransactionVolume
FROM rets
WHERE Record.StandardStatus = "Closed" and Record.Dates.CloseDate >= '2019-01-01' and  Record.Dates.CloseDate < '2020-01-01'
GROUP BY Record.Agents.ListingAgent.ListAgentKeyNumeric,
         Record.Agents.ListingAgent.ListAgentFirstName,
         Record.Agents.ListingAgent.ListAgentLastName
ORDER BY COUNT(*) DESC
2 
SELECT agent, month,
       COUNT(1) AS NbrofTransactions,
       SUM(Record.Prices.ClosePrice) AS TransactionVolume
FROM rets
LET agent = CONCAT(Record.Agents.ListingAgent.ListAgentFirstName, " " , Record.Agents.ListingAgent.ListAgentLastName),
    month = DATE_PART_STR(Record.Dates.CloseDate,"month")
WHERE Record.StandardStatus = "Closed" and Record.Dates.CloseDate >= '2019-01-01' and  Record.Dates.CloseDate < '2020-01-01'
GROUP BY agent, month
ORDER BY NbrofTransactions  DESC;

Duplicate names want to add ListAgentKeyNumeric you can add in GROUP BY and projection

3

I don’t want to sound greedy, but is there a way to combine 2 queries like the above one and this one ?

SELECT agent,
       month,
       COUNT(1) AS NbrofTransactions,
       SUM(TO_NUMBER(Record.Prices.ClosePrice)) AS TransactionVolume
FROM rets
LET agent = CONCAT(Record.Agents.BuyerAgent.BuyerAgentFirstName, " " , Record.Agents.BuyerAgent.BuyerAgentLastName),
    month = DATE_PART_STR(Record.Dates.CloseDate,"month")
WHERE Record.StandardStatus = "Closed"
    AND Record.Dates.CloseDate >= '2019-01-01'
    AND Record.Dates.CloseDate < '2020-01-01'
    AND Record.Address.CountyOrParish = "Orange"
GROUP BY agent,
         month
ORDER BY agent DESC

difference is the one will have the sales as a seller agent, the other as a buyer agent which is either
Record.Agents.BuyerAgent.BuyerAgentFirstName, " " , Record.Agents.BuyerAgent.BuyerAgentLastName
or
Record.Agents.ListingAgent.ListAgentFirstName, " " , Record.Agents.ListingAgent.ListAgentLastName

and i would ideally want a separate count and sum per month and agent

4

You can do UNION or UNION ALL and if needed you can sort on parent query.

SELECT d.* 
FROM (SELECT agent, month,
                         COUNT(1) AS NbrofTransactions,
                         SUM(Record.Prices.ClosePrice) AS TransactionVolume
             FROM rets
           LET agent = CONCAT(Record.Agents.ListingAgent.ListAgentFirstName, " " , 
                                      Record.Agents.ListingAgent.ListAgentLastName),
                      month = DATE_PART_STR(Record.Dates.CloseDate,"month")
            WHERE Record.StandardStatus = "Closed" and Record.Dates.CloseDate >= '2019-01-01' and  Record.Dates.CloseDate < '2020-01-01'
          GROUP BY agent, month
     UNION ALL
           SELECT agent,
                     month,
                    COUNT(1) AS NbrofTransactions,
                    SUM(TO_NUMBER(Record.Prices.ClosePrice)) AS TransactionVolume
          FROM rets
         LET agent = CONCAT(Record.Agents.BuyerAgent.BuyerAgentFirstName, " " , Record.Agents.BuyerAgent.BuyerAgentLastName),
              month = DATE_PART_STR(Record.Dates.CloseDate,"month")
       WHERE Record.StandardStatus = "Closed"
    AND Record.Dates.CloseDate >= '2019-01-01'
    AND Record.Dates.CloseDate < '2020-01-01'
    AND Record.Address.CountyOrParish = "Orange"
       GROUP BY agent, month ) AS d
ORDER BY d.agent DESC;

If Each document either BuyerAgent or ListingAgnet possible you can do conditional in CONCAT with single query
agent = CONCAT( CASE Buyer THEN buyerFisrtname ELSE ListingFirstName END, …)

5

This works somewhat, is there a way to force the output to create even a row per month per agent even if he has no sale ? The reason i ask because it would make it easier to feed into a graph or spread sheet. In current query we only get data if an agent has a sale and it falls in a certain month.