Thanks a lot for your time (and your previous help ;))
The problem in itself, is pretty much what you describe, maybe I'm looking at it wrong, but results doesn't match up in your example
Although I only have one table, I need to output a string 'NO DATA' if no rows matches my select.
So, I need to check all the dates and if no dates matches `enne` and `user` then output 'NO DATA'.
if a date matches `enne` and `user` AND score>2 then count it.
if a date matches `enne` and `user` BUT score<=2 then don't count it.
A modification of your example with desired output of both 'enne' and 'ennehead'.
+----+----------+------------+-------+-------+------------+
| id | enne | date | score | user | department |
+----+----------+------------+-------+-------+------------+
| 13 | ennehead | 2019-10-01 | 1 | fred | sales |
| 16 | head | 2019-10-01 | 1 | fred | sales |
| 17 | ennehead | 2019-10-01 | 3 | curly | accts |
| 18 | ennehead | 2019-10-01 | 5 | mo | accts |
| 20 | ennehead | 2019-10-02 | 3 | pete | sales |
| 21 | ennehead | 2019-10-02 | 5 | mary | sales |
| 22 | head | 2019-10-02 | 1 | fred | sales |
| 23 | ennehead | 2019-10-02 | 3 | curly | accts |
| 24 | ennehead | 2019-10-02 | 5 | mo | accts |
| 25 | ennehead | 2019-10-03 | 5 | fred | accts |
| 26 | ennehead | 2019-10-03 | 5 | fred | accts |
| 27 | ennehead | 2019-10-03 | 5 | mo | accts |
+----+----------+------------+-------+-------+------------+
+------------+---------+
| date | head |
+------------+---------+
| 2019-10-01 | 0 |
| 2019-10-02 | 0 |
| 2019-10-03 | NO DATA |
+------------+---------+
+------------+----------+
| date | ennehead |
+------------+----------+
| 2019-10-01 | 0 |
| 2019-10-02 | NO DATA |
| 2019-10-03 | 2 |
+------------+----------+
It is vital that I get, a result for each distinct date. 0 if no row has a score > 2, and 'NO DATA' if no rows are found matching `enne` and `user` for a date.
the distinct dates: [2019-10-01, 2019-10-02, 2019-10-03]
the count for each 'enne' - head: [0, 0, NO DATA]
the count for each 'enne' - ennehead: [0, NO DATA, 2]
This would be the result for a user name 'hercules'
+------------+----------+
| date | head |
+------------+----------+
| 2019-10-01 | 0 |
| 2019-10-02 | 0 |
| 2019-10-03 | 0 |
+------------+----------+
Which would be wrong as user 'hercules' doesn't exists. it should output:
+------------+----------+
| date | head |
+------------+----------+
| 2019-10-01 | NO DATA |
| 2019-10-02 | NO DATA |
| 2019-10-03 | NO DATA |
+------------+----------+
Hope it clears up everything