Previously on this forum, I got some v. useful help from Guido about running a counting SQL on a table - getting this result.
However, I use ASP on my site, and the host only has a MySQL ODBC 3.51 Driver, meaning it can’t handle the v. useful sum(…) method that Guido suggested.
On looking for an alternative, I can start out basic, with:
SELECT
DATE(card_date) AS a_date
, DATE_FORMAT(card_date
, '%W %d %M %Y') AS b_date
, DATE_FORMAT(card_date, '%Y-%m-%d') AS f_date
, COUNT(*) ct_all
FROM ecards c
GROUP BY DATE(card_date)
ORDER BY 1 DESC LIMIT 10;
I was then thinking of putting the result of this qry into a recordset, and looping through it, and each time through the loop, doing a count for other things - e.g.:
SELECT
COUNT(*) AS ct_vid
FROM ecards
WHERE flag_vid = 1
AND date(card_date) = &value_from_loop
However, I wondered if I could just do a sub-select instead - e.g.:
SELECT
DATE(card_date) AS a_date
, DATE_FORMAT(card_date
, '%W %d %M %Y') AS b_date
, DATE_FORMAT(card_date, '%Y-%m-%d') AS f_date
, COUNT(*) ct_all
, (SELECT COUNT(*) FROM ecards WHERE flag_vid = 1) AS ct_vid
FROM ecards c
GROUP BY DATE(card_date)
ORDER BY 1 DESC LIMIT 10;
But obviously that’s flawed, because the sub-select will always count +ALL+ the values from ecards WHERE flag_vid = 1, meaning I get output like this:
a_date b_date f_date ct_all ct_vid
-------------------------------------------------------------------------------
2010-06-11 Friday 11 June 2010 2010-06-11 8 4198
2010-06-10 Thursday 10 June 2010 2010-06-10 103 4198
2010-06-09 Wednesday 09 June 2010 2010-06-09 127 4198
I can’t work out how to get the sub-select to only return the count for the day in question - i.e. the date that is being returned for the main query within which it sits.
Sorry for the essay.
Any advice much appreciated!
Thanks