locked
SQL Count Distinct Values For Date RRS feed

  • Question

  • User-376597385 posted

    

    Hello everyone,

    I have the table 'Transactions' containing all the transactions in an year. The columns of the table are:
    Transaction_date
    Type
    Value
    Payer_Id
    Status

    What I would like to do is a query to group up all the transactions by month, then count the transactions corresponding to each month, Sum up their values and count the tansaction dates (as number of days per monh) for each month.
    The problem is that there are numerous transactions with the same date and I need to "Distinct Count" them.
    The code I came up with is below, but it gives me an error when saving the query: 'Join expression not supported'

    SELECT Format([Transaction_date],"MM-YYYY") AS Month
    , Count(Transactions.Type) AS [Number of transactions]
    , Sum(Transactions.Value) AS [Value]
    , TCounts.TCount
    FROM Transactions INNER JOIN 
           (SELECT Count(Transaction_Date) as TCount, MonthYear
           FROM (
                    SELECT DISTINCT (Transaction_Date)
                    , Format([Transaction_Date],"mm-yyyy") as MonthYear
                    FROM Transactions
                   WHERE (Transactions.Status) = "Complete" and (Transactions.Payer_ID) <> ""
                   GROUP BY MonthYear
                    )
           ) as TCounts
    ON  Format([Transaction_date],"MM-YYYY") = TCounts.MonthYear
    WHERE (Transactions.Status) = "Complete" and (Transactions.Payer_ID) <> ""
    GROUP BY Format([Transaction_date],"MM-YYYY"), TCounts.TCount;

     

     

    Wednesday, April 20, 2011 10:06 AM

Answers

  • User1447889451 posted

    Hmm, seems odd, however, now I look at it again I made a mistake

    The outer select statement doesn't need date part.

     

    Try this

    SELECT      Year_Part,
        Month_Part,
        COUNT(*) as Transaction_Count,
        SUM(Transactions.Value) as Transaction_Total
    FROM    (
    SELECT    DATEPART("yyyy", [Transaction_date]) as Year_Part,
        DATEPART("mm", [Transaction_date]) as Month_Part,
        DATEPART("dd", [Transaction_date]) as Day_Part,
        SUM(Value) as Transaction_Total
    FROM Transactions
    WHERE    Transactions.Status = "Complete"
        AND Transactions.Payer_ID <> ""
    GROUP BY
        DATEPART("yyyy", [Transaction_date]),
        DATEPART("mm", [Transaction_date]),
        DATEPART("dd", [Transaction_date])
        ) as d
    GROUP BY
        Year_Part,
        Month_Part;

     

    If you still get the error, break the query into parts. Try the inner query on it's own.

     

    Fran

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 20, 2011 4:13 PM

All replies

  • User1447889451 posted

    How about this. It's TSQL format, but you should be able to complete the same principle in MS Access with only a few alterations.

    SELECT			DATEPART(yyyy, Transaction_date) Year_Part, 
    				DATEPART(mm, Transaction_date) Month_Part,
    				COUNT(*) Transaction_Count,
    				SUM(Transaction_Total) Transaction_Total
    FROM			(
    					SELECT			DATEPART(yyyy, Transaction_date) Year_Part, 
    									DATEPART(mm, Transaction_date) Month_Part, 
    									DATEPART(dd, Transaction_date) Day_Part, 
    									SUM(Value) Transaction_Total
    					FROM			Transactions
    					WHERE			[Status] = 'Complete' 
    					AND				Payer_ID <> ''
    					GROUP BY		DATEPART(yyyy, Transaction_date),
    									DATEPART(mm, Transaction_date), 
    									DATEPART(dd, Transaction_date)
    				) d
    GROUP BY		DATEPART(yyyy, Transaction_date),
    				DATEPART(mm, Transaction_date)
    				

    First get the day data right (i.e. one record per day) and then aggregate up from that

    Any good?

    Wednesday, April 20, 2011 10:37 AM
  • User-376597385 posted

    Ok, I worked on your solution and the resulting code is:



    SELECT     DATEPART("yyyy", [Transaction_date]) as Year_Part,
        DATEPART("mm", [Transaction_date]) as Month_Part,
        COUNT(*) as Transaction_Count,
        SUM(Transactions.Value) as Transaction_Total
    FROM    (
    SELECT    DATEPART("yyyy", [Transaction_date]) as Year_Part,
        DATEPART("mm", [Transaction_date]) as Month_Part,
        DATEPART("dd", [Transaction_date]) as Day_Part,
        SUM(Value) as Transaction_Total
    FROM Transactions
    WHERE    Transactions.Status = "Complete"
        AND Transactions.Payer_ID <> ""
    GROUP BY
        DATEPART("yyyy", [Transaction_date]),
        DATEPART("mm", [Transaction_date]),
        DATEPART("dd", [Transaction_date])
        ) as d
    GROUP BY
        DATEPART("yyyy", [Transaction_date]),
        DATEPART("mm", [Transaction_date]);
    When I try to run it, an error message pops up saying that:
    'You tried to execute a query that does not include the specified expression
    DATEPART("yyyy", [Transaction_date])
    as part of an aggregate function.

    Any ideas why?

     

    Wednesday, April 20, 2011 11:41 AM
  • User1447889451 posted

    Hmm, seems odd, however, now I look at it again I made a mistake

    The outer select statement doesn't need date part.

     

    Try this

    SELECT      Year_Part,
        Month_Part,
        COUNT(*) as Transaction_Count,
        SUM(Transactions.Value) as Transaction_Total
    FROM    (
    SELECT    DATEPART("yyyy", [Transaction_date]) as Year_Part,
        DATEPART("mm", [Transaction_date]) as Month_Part,
        DATEPART("dd", [Transaction_date]) as Day_Part,
        SUM(Value) as Transaction_Total
    FROM Transactions
    WHERE    Transactions.Status = "Complete"
        AND Transactions.Payer_ID <> ""
    GROUP BY
        DATEPART("yyyy", [Transaction_date]),
        DATEPART("mm", [Transaction_date]),
        DATEPART("dd", [Transaction_date])
        ) as d
    GROUP BY
        Year_Part,
        Month_Part;

     

    If you still get the error, break the query into parts. Try the inner query on it's own.

     

    Fran

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 20, 2011 4:13 PM
  • User-376597385 posted

    Hi Fran,

    I finally got it! The code works like a charm. I had to make some adjustments...

    It doesn't run too slow, it takes only a few seconds to go through over 2.5 million transactions, so I think it's quite ok...

    Here's the final code, maibe somebody else will make use of it:

     

    SELECT d.Year, d.Month, Count(*) AS [Number_of_days], Sum(d.Numer_of_transactions) AS Volume, Sum(d.Value) AS Total
    FROM [SELECT DatePart("yyyy",Transactions!Transaction_date) AS Year, DatePart("m",Transactions!Transaction_date) AS Month
    , DatePart("d",Transactions!Transaction_date) AS Day
    , Count(Transactions!Transaction_type)  as Number_of_transactions
    , Sum(Transactions!Transaction_value) AS Value
    FROM Transactions
    WHERE (((Transactions.Status)="Complete") AND ((Transactions.Payer_ID)<>""))
    GROUP BY DatePart("yyyy",Transactions!Transaction_date), DatePart("m",Transactions!Transaction_date), DatePart("d",Transactions!Transaction_date) 
               ]. AS d
    GROUP BY d.Year, d.Month;
    
    

    Thank you very much for your time and for the very original solution!

     

    Thursday, April 21, 2011 3:52 AM