Getting 2 counts from 1 table using join?

[dannylewin]

@phobucket

This will get it in 1 row.

SELECT
EXTRACT(YEAR_MONTH FROM stat_date) AS yrmth
SUM(CASE WHEN stat_type = ‘hit’ THEN 1 ELSE 0 END) as hits,
SUM(CASE WHEN stat_type = ‘email’ THEN 1 ELSE 0 END) as emails
FROM stats
WHERE dress_id = 937
GROUP
BY yrmth

Thank you SO very much. Works EXACTLY like i wanted!!!

[dannylewin]

Next quick question, is how do i show stats for the last 7 days?

Would i keep everything the same and just add a between DATE_SUB INT 7 days and now?

[phobucket]

add this to the where clause

AND stat_date BETWEEN ADD_DATE( CURRENT_DATE, -7) AND CURRENT_DATE 

You could do " AND stat_date >= ADD_DATE( CURRENT_DATE, -7) " if you know that there will not be dates greater than the current date in the system