Alright,
Here’s my conundrum. I’ve grabbed rows by date and date range before, but is it possible to do multiple COUNT in a single query.
Like, I want to know how many rows have been added that day, that week, that month, and that year. And get that information all in a single query.
Possible? And, if so, how resource intensive is it.
Thanks for the help
Ryan
if you aren’t using a WHERE clause, your query will do a table scan
my query’s WHERE clause allows an index on the date column to be utilized, to restrict the results to the current year only
300K times per day??? you should consider designing a table to hold statistical results… no sense in counting the same things over and over
possible, and not at all resource intensive if your date column has an index (for the WHERE clause)
SELECT COUNT(*) AS year_count
, COUNT(CASE WHEN MONTH(somedate) =
MONTH(CURRENT_DATE)
THEN 'curly'
ELSE NULL END) AS month_count
, COUNT(CASE WHEN WEEKOFYEAR(somedate) =
WEEKOFYEAR(CURRENT_DATE)
THEN 'larry'
ELSE NULL END) AS week_count
, COUNT(CASE WHEN somedate =
CURRENT_DATE
THEN 'moe'
ELSE NULL END) AS day_count
FROM daTable
WHERE somedate >= CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
AND somedate < CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
+ INTERVAL 1 YEAR
lol. fine. I’ll just use yours.
I did, but I had to change:
COUNT(CASE WHEN trailers.date =
CURRENT_DATE
THEN ‘moe’
ELSE NULL END) AS day_count
to
COUNT(CASE WHEN trailers.date >=
CURRENT_DATE
THEN ‘moe’
ELSE NULL END) AS day_count
for it to acknowledge/count today’s updates. (it was leaving it at 0)
Cheers!
Ryan
Yes, indeedy 
Thanks again
Ryan
um, no 
the WHERE clause merely restricts the rows to the current year
replace your IFs with CASE expressions and it’s looking more and more like post #2 all the time…
You are right.
Dang. hmmm
Okay, I’ve updated the where clause with the date range. can I just update my IF statements to grab current month and not last 30/31 days?
SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH),1,0))
would become…
SELECT COUNT(trailers.trailer_id) AS videosyear,
SUM(IF(trailers.date >= CURRENT_DATE,1,0)) AS videostoday,
SUM(IF(WEEKOFYEAR(trailers.date) = WEEKOFYEAR(CURRENT_DATE),1,0)) AS videosweek,
SUM(IF(MONTH(trailers.date)= MONTH(CURRENT_DATE),1,0)) AS videosmonth
FROM trailers
WHERE trailers.date >= CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
AND trailers.date < CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY
+ INTERVAL 1 YEAR
Does that work? It certainly runs okay, and the WHERE clause makes sure there is no overlap on the MONTH WEEK and all that.
Look good?
Thanks so much!
Ryan
ah, so you store a datetime value in a column called date 
oh, and by the way, your counts are all wrong
subtracting INTERVAL 1 MONTH from today’s date does ~not~ give you the current month, it gives you the previous 30/31 days
if i read your first post, i think i got the right totals and you didn’t
Wow. That is definitely different than the approach I went with.
What you think of this part:
SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),1,0)) AS videostoday,
SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK),1,0)) AS videosweek,
SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH),1,0)) AS videosmonth,
SUM(IF(trailers.date>= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR),1,0)) AS videosyear
The above worked. But less efficient than yours?
Cheers!
Ryan
The use of CASE is SQL standard. The use of IF as you have done, will work in MySQL but won’t if you switch to another database down the road.
Thanks for that update.
In terms of efficiency (use of server resources), is one option better than the other?
This query will likely run about 300K times per day. So need it to not be wasteful.
Cheers!
Ryan