Need to count unique dates (1 Viewer)

[Craig Alan]

Novice, losing my hair.

Simple DB, one table, tracking stock trades, one trade per row, date column along with many others. Each trade gets a date. Often I make more than one trade a day and I want to calculate profit/loss per day. So, I need to get a total of unique dates. "Count" gives me the number of date entries and not the number of unique dates. I can get a listing of unique dates in a query but not a total. A simple query of the query won't count them as they're not numeric.

So, please, how can I get the total number of unique dates from a column?

Thanks in advance.

Craig

 

[plog]

You will need a subquery to get unique dates. Then you use your COUNT query on it to determine how many you have.

SELECT TradeDate FROM YourTableHere GROUP BY TradeDate

Call that 'sub1'. Then use it in antoher query and use your COUNT method.

 

[MarkK]

Or even SQL like...

SELECT Count(*) As MyCount 
FROM 
   (
   SELECT TradeDate 
   FROM YourTableHere 
   GROUP BY TradeDate
   )

...with the subquery embedded in the Count query...

Happy Holidays

 

[Frothingslosh]

Personally I'd recommend plog's solution simply because Mark's runs afoul of Access's REALLY annoying habit of running in-line subqueries once for reach record, which can chew up a LOT of time if you're dealing with a decently-sized recordset.

Now if you ever find yourself working with SQL Server, Mark's solution is perfect, because SQL Server runs the subquery once and holds the data while the rest is processed.

 

[Craig Alan]

Many thanks to all. I used Plog's solution and it killed this problem off! A cold beer for each of you.

 

[MarkK]

...Access's REALLY annoying habit of running in-line subqueries once for reach record

This query...

SELECT Count(*) As MyCount 
FROM 
   (
   SELECT TradeDate 
   FROM YourTableHere 
   GROUP BY TradeDate
   )

...only runs the subquery once.
Happy new year.

 

[Frothingslosh]

Huh. Then it's the first one I've seen Access actually handle like that, which is why I avoid that approach like the plague in Access. I can see why that probably is, though.

But man, over the years I have, as a rule, seen improvements by going from subqueries to stacked queries that have averaged in the order of 90%+ reduction in processing time because of that little quirk of the Jet engine. (The biggest one I can remember was one query dropping from 10 minute processing time to ~5 seconds by shifting the subquery into its own query and using it as its own source. Blew my freaking mind.)