Counting Query

Databases
1

I have some MySQL tables which store ecard details.

There are 2 main tables - the table storing the ecards (ecards), and another table storing the ecard designs (photos).

The photos table has a themeType column. The 2 main types are ‘v’ for video, and ‘f’ for flash.

I’d like to do a simple count of the number of ecards sent per day, with 3 counts:

e.g. - ideal output:

Date		ct_all		ct_vid		ct_flash
-----------------------------------------------------------------
2010-06-09	100		40		60
2010-06-08	95		15		80
2010-06-07	120		90		30

I can do a simple count like this:

SELECT 
  DATE(card_date), COUNT(*) ct_all
FROM card c, photos p
WHERE c.photoID = p.photoID
GROUP BY DATE(card_date)
ORDER BY 1 DESC;

Date		ct_all	
------------------------
2010-06-09	100	
2010-06-08	95	
2010-06-07	120

But I’m not sure how to do the split into the ct_vid (which would have an additional line in the SQL: AND p.themeType = ‘v’) and ct_flash (AND p.themeType = ‘f’);

The only way I’ve been able to do it before is to do a simple thing like this first:

SELECT DATE(card_date)
FROM card c, photos p
WHERE c.photoID = p.photoID
ORDER BY 1 DESC
LIMIT 3;

DATE(card_date)
--------------------------
2010-06-09
2010-06-08
2010-06-07

Then do the processing in a loop in a scripting language (ASP) on a web page

e.g.

loop through “2010-06-09” date, run 2 SQLs for that day:

SELECT COUNT(*)
from card c, photos p
where c.photoID = p.photoID
AND p.themeType = 'v'
AND date(c.card_date) = "2010-06-09";

SELECT COUNT(*)
from card c, photos p
where c.photoID = p.photoID
AND p.themeType = 'f'
AND date(c.card_date) = "2010-06-09";

My whole approach seems super crappy though - i.e. having to take stuff into a scripting language and do loops to do it.

I wondered if there is a slick way I could do the whole thing in SQL instead?

Sorry for the essay.

Any advice much appreciated.

Thanks!

2

Wow! Thank you for your help.

Have I done the SQL join incorrectly, or is the INNER JOIN comment a joke?

I admit though, that the SQL I write is usually a load of crap, so probably the former!

Thanks again for your prompt response.

3

copy&paste… :smiley:
Hey, it’s late :wink:

4

guido, INNER JOIN, please :slight_smile:

5 

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;
6

it’s correct, but the INNER JOIN syntax is better for a number of reasons