Counting Query - grouping issue

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

The remote, live server MySQL version is at 5.0.19-log.

My local MySQL is 5.1.41-community.

However, the SQL that powers the query +will+ work directly in phpMyAdmin, but doesn’t work once plugged into the ASP page. I can only assume it’s because the live site is running an older version of the MySQL ODBC Driver…

It’s strange - I could run your query in SQLYog and phpMyAdmin, but when I ran the SQL within my ASP page, and tried to assign variables to it, there was no output.

It was very strange, but that was definitely going on, because if I did this:

SELECT
    DATE(card_date)
  , COUNT(*) ct_all
  , SUM(CASE WHEN p.themeType = 'v' THEN 1 ELSE 0 END) AS ct_vid
  , SUM(CASE WHEN p.themeType = 'f' THEN 1 ELSE 0 END) AS ct_flash
FROM card c, photos p
WHERE c.photoID = p.photoID
GROUP BY DATE(card_date)
ORDER BY 1 DESC;

And plugged it into the ASP page, then I would get no output at all… as in, the recordset was empty.

If I did the same SQL, with the SUMS removed, it worked, and the recordset was populated.

This is my connection string in ASP:

Dim oConn
set oConn = Server.CreateObject(“ADODB.Connection”)
oConn.Open “DSN=MSQ10832; UID=dba1083; PWD=301an82c;”

And I’d populate my recordset via something like:

SQL = “SELECT this FROM that”
set myRecordSet = oConn.Execute(SQL)

It’d be lots better if I was using PHP, but I never learnt it, and have been using ASP for years now. Never too late to learn something new though!

Thanks

Jim

What version of MySQL is the (actual) server using?

There’s no error at all. It’s just that as soon as I include the (SUM)… bits, the resulting recordset is empty. There’s nothing to debug unfortunately.

Serves me right for using ASP in the first place!

What error (if any) is MySQL giving, you need whatever asp’s version of php’s mysql_error() is.

Let me start by saying that I never used ODBC, at least not consciously :smiley: , but why wouldn’t it let you use SUM?