Query COUNT (*)

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:

Code:

---

+------+--------+
| 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:

Code:

---

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

---

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.

Code:

---

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.

thank you very much.

this is the new output, but I don't have the total ... :

Code:

---

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

---

Quote:

---

Originally Posted by cms9651

...but I don't have the total

---

hint: ROLLUP

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

Code:

---

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

---

to this --

Code:

---

COALESCE(YEAR(myDate),2001)

---

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

:D

Wow!

thank you very much!

Code:

---

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

---