Help with Distinct and Count

[cseasy]

Hi,

I have a table with a DATETIME field to log activity.

I want to write a query that returns (up to) the most recent five unique months of activity, but that also shows my the total number of unique months so that if there are more than 5, I can show a 'more' button in my application.

The query I started with is:

SELECT
DATE_FORMAT(activityDate, '%M, %Y') AS activityDate, COUNT(DATE_FORMAT(activityDate, '%M, %Y')) AS qty
FROM
activity
WHERE
activityDate <= '2006-07-20 00:00:00'
GROUP BY
DATE_FORMAT(activityDate, '%M, %Y')
ORDER BY
activityDate DESC
LIMIT
5

But this only shows the qty as the number of items within the month in that row.

So then I tried:

SELECT
DATE_FORMAT(activityDate, '%M, %Y') AS activityDate, COUNT(*) AS qty
FROM
activity
WHERE
activityDate <= '2006-07-20 00:00:00'
GROUP BY
DATE_FORMAT(activityDate, '%M, %Y')
UNION
SELECT DISTINCT
'total' AS activityDate, COUNT(DATE_FORMAT(activityDate, '%M, %Y')) AS qty
FROM
activity
WHERE
activityDate <= '2006-07-20 00:00:00'
ORDER BY
activityDate DESC
LIMIT
6

This gives me a row at the top of my recordset with 'total' in the 'activityDate' field, but the 'qty' field shows a total of all unique dates, not just 'month, year' (so for example, if I have three items in July '06, instead of counting July '06 as 1 it's adding 3 to the total, essentially ignoring my DISTINCT.)

I'm sure I'm missing something extremely obvious and any help would be greatly appreciated.

Thanks,
C.

[guelphdad]

DISTINCT does not work as a funciton, it works across the entire row of columns that you select.

how about showing some sample data and sample output you are looking to return?

[cseasy]

My 'activity' table may have the following


activityId activityDate userId
---------- ------------------- ------
1 2006-01-01 10:01:00 1
2 2006-02-01 14:45:00 2
3 2006-03-01 16:57:00 3
4 2006-04-01 15:17:00 1
5 2006-05-01 07:03:00 2
6 2006-06-01 19:06:00 3
7 2006-07-01 20:10:00 1
8 2006-07-02 15:22:00 2
9 2006-07-03 14:38:00 3


There are 7 unique months in there.

I'd like the following details back (the 5 most recent months, plus a count of the total number of unique months so I know whether to show a 'more; button on my app.). Either:


activityDate qty
------------ ---
July, 2006 7
June, 2006 7
May, 2006 7
April, 2006 7
March, 2006 7


Or


activityDate qty
------------ ---
total 7
July, 2006 3
June, 2006 1
May, 2006 1
April, 2006 1
March, 2006 1


Or a better suggestion :)

Thanks in advance,
C.