[nonhuman]

So I have a table in a MySQL database that contains a bunch of entries each of which has a date. I want to be able to display some basic statistics on that table. In order to display the number of entries each month I have the following query:

Code:
SELECT COUNT(*) AS count, DATE_FORMAT(date, '%m/%Y') AS month FROM my_table WHERE site='xxxx' GROUP BY month;

This works great and gives me output like this:

Code:
+-------+---------+
| count |  month    |
+-------+---------+
|       1   | 06/2004 |
|       3   | 06/2005 |
+-------+---------+
2 rows in set (0.00 sec)

But what I really want is to have not only those numbers, but also the grand total of all entries. Basically, I want to also get SUM(count). But I want to do it all in one query (I have my reasons).

So something like this:

Code:
SELECT SUM(count), count, month FROM (SELECT COUNT(*) as count, DATE_FORMAT(date, '%m/%Y') AS month FROM my_table WHERE site='xxxx' GROUP BY month) AS foo;

But of course that doesn't work because I'm mixing group and non-group columns without a GROUP BY.

Can anyone help me out with this? Is it even possible?

[khufuu]

SELECT COUNT(*) AS count, DATE_FORMAT(date, '%m/%Y') AS month FROM my_table WHERE site='xxxx' GROUP BY month COMPUTE SUM(COUNT(*))

This works on my MS SQL Server 2000. You should have some equivalent on your system.

[MacAtak]

As far as I know MySQL has no equivalent for "COMPUTE".

If you have MySQL 4.1 you could do a sub select, like this:

Code:
SELECT COUNT(*) AS count, DATE_FORMAT(date, '%m/%Y') AS month, (SELECT COUNT(*) FROM `my_table`) as `total` FROM my_table WHERE site='xxxx' GROUP BY month;

This would give you something like this:

Code:
+-------+---------------+
| count | month | total |
+-------+---------------+
| 1 | 06/2004 | 4 |
| 3 | 06/2005 | 4 |
+-------+---------------+
2 rows in set (0.00 sec)

[dax]

i am very new to sql so i appreciate any help one might be able to give.

basically what i am trying to achieve is, counting the number of new users that signup each month for a statistics page. and then also generate the total for the year. I would love to accomplish this in one sql query but i am not sure how to go about it.

currently i have the following statement in a for loop that access the database 12 times for each of the months. Hopefully there is a better solution.

$newclients_sql = mysql_query("SELECT COUNT(*) FROM ClientInfo WHERE FirstLogin >= '$currentMonth_Beg' AND FirstLogin <= '$currentMonth_End'")

thanks

[MacAtak]

@dax, welcome!

I just ran into a solution for both of these situations using GROUP BY and WITH ROLLUP. I don't know if I can do any better at explaining it than the MySQL doc so here is the link.

GROUP BY Modifiers

If you can't make sense of the docs, let me know. I'll try to explain it.

[dax]

thanks for your response. I looked over the GROUP BY page and i pretty much understand everything. I am still not exactly sure how to handle the date though.

in my database i have my date stored as the following ex. Year-MM-DD. If I want to GROUP BY month, how exactly would i write this out?

thanks.

[MacAtak]

Check out the MySQL Date-Time functions.

You can EXTRACT a type from a date, such as:

Code:
SELECT EXTRACT(MONTH FROM '2005-07-01') as `month`
FROM `table`
GROUP BY `month`

I'm pretty sure that this will work.