Query COUNT (*)

Databases
1

Hi there, I need your help.

I tried this query but I need other type of output.
Can you help me?
thank you

Correct output:

+------+--------+
| year | number |
+------+--------+
| 2001 |   2987 |
| 2003 |    364 |
| 2004 |    400 |
| 2005 |    217 |
| 2006 |    135 |
| 2007 |    143 |
| 2008 |    225 |
| 2009 |    261 |
| 2010 |    393 |
| 2011 |    363 |
| 2012 |    253 |
| 2013 |      1 |
| tot_ |   5542 |
+------+--------+

My query:


mysql> SELECT
	CASE
WHEN YEAR (myDate) IS NULL
OR YEAR (myDate) = '2001' THEN
	'2001'
ELSE
	YEAR (myDate)
END AS year,
 COUNT(*) AS number
FROM
	tbl_login
GROUP BY
	YEAR (myDate);
+------+--------+
| year | number |
+------+--------+
| 2001 |   2986 |
| 2001 |      1 |
| 2003 |    364 |
| 2004 |    400 |
| 2005 |    217 |
| 2006 |    135 |
| 2007 |    143 |
| 2008 |    225 |
| 2009 |    261 |
| 2010 |    393 |
| 2011 |    363 |
| 2012 |    253 |
| 2013 |      1 |
+------+--------+
13 rows in set
2

Why not just fix that one date in the table?

For learning purposes though, your grouping by the actual column instead of the new case statement you made.


group by 

CASE WHEN YEAR (myDate) IS NULL OR YEAR (myDate) = '2001' THEN     '2001'

and then if you want an actual row to show the total, use a union all to add a new count to the end.

3

thank you very much.

this is the new output, but I don’t have the total … :

mysql> SELECT
	CASE
WHEN YEAR (myDate) IS NULL
OR YEAR (myDate) = '2001' THEN
	'2001'
ELSE
	YEAR (myDate)
END AS year,
 COUNT(*) AS number
FROM
	tbl_login
GROUP BY
	CASE
WHEN YEAR (myDate) IS NULL
OR YEAR (myDate) = '2001' THEN
	'2001'
ELSE
	YEAR (myDate)
END;
+------+--------+
| anno | number |
+------+--------+
| 2001 |   2987 |
| 2003 |    364 |
| 2004 |    400 |
| 2005 |    217 |
| 2006 |    135 |
| 2007 |    143 |
| 2008 |    225 |
| 2009 |    261 |
| 2010 |    393 |
| 2011 |    363 |
| 2012 |    253 |
| 2013 |      1 |
+------+--------+
12 rows in set
4

hint: ROLLUP

5

by the way, YEAR() produces an integer, you are making extra work for mysql by comparing it to a string

also, your CASE can be simplified

change this –

CASE
WHEN YEAR (myDate) IS NULL
OR YEAR (myDate) = '2001' THEN
	'2001'
ELSE
	YEAR (myDate)
END;

to this –

COALESCE(YEAR(myDate),2001)
6

final thought: now watch what happens when you try to combine ROLLUP with that hack for the null year

:smiley:

7

Wow!

thank you very much!

mysql> SELECT
	COALESCE (myYear, 'tot') AS `myYear`,
	number
FROM
	(
		SELECT
			COALESCE (YEAR(myDate), 2001) AS myYear,
			COUNT(*) AS number
		FROM
			tbl_login
		GROUP BY
			COALESCE (YEAR(myDate), 2001) WITH ROLLUP
	) q;

+------+--------+
| myYear | number |
+------+--------+
| 2001 |   2987 |
| 2003 |    364 |
| 2004 |    400 |
| 2005 |    217 |
| 2006 |    135 |
| 2007 |    143 |
| 2008 |    225 |
| 2009 |    261 |
| 2010 |    393 |
| 2011 |    363 |
| 2012 |    253 |
| 2013 |      1 |
| tot  |   5742 |
+------+--------+
13 rows in set