Counting Query - grouping issue

Databases
1

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

2

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…

3

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

4

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

5

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!

6

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

7

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