SQL Count Distinct Values For Date

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;

 

 

Answer 1

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?

Answer 2

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?

 

Answer 3

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

Answer 4

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!