Outputting chosen string instead of zero?

[kilnakorr]

Hi

 

I have previously been using the below code for getting a zero value when no results where found:

SUM(CASE WHEN `foo`='bar' then 1 else 0 end) as something.....

Currently, I need an output other than 0, as the result could be 0, and I have no way of telling an actually 0 value from a null.

How can I output my chosen string, so a 0 value is outputted as 0 and a null value is outputted as 'no value' (as string)?

Hope it makes sense

[Barand]

Not sure I totally understand. If you had this situation below, what output would you want to see?

SELECT * FROM kilnakorr;
+----+------+------+------+------+
| id | cola | colb | colc | cold |
+----+------+------+------+------+
|  1 | bar  | foo  | bar  | NULL |
|  2 | foo  | foo  | NULL | NULL |
|  3 | foo  | foo  | bar  | NULL |
|  4 | bar  | foo  | NULL | NULL |
+----+------+------+------+------+

SELECT SUM(CASE WHEN cola = 'bar' THEN 1 ELSE 0 END) as tota
     , SUM(CASE WHEN colb = 'bar' THEN 1 ELSE 0 END) as totb
     , SUM(CASE WHEN colc = 'bar' THEN 1 ELSE 0 END) as totc
     , SUM(CASE WHEN cold = 'bar' THEN 1 ELSE 0 END) as totd
FROM kilnakorr;
+------+------+------+------+
| tota | totb | totc | totd |
+------+------+------+------+
|    2 |    0 |    2 |    0 |      (What do you want to see here?)
+------+------+------+------+

 

Edited October 27, 2019 by Barand

[kilnakorr]

Let me show you my code to better explain.

SUM(CASE WHEN `enne`='ennehead' AND score>2 AND user='$username' THEN 1 ELSE 0 END) AS head FROM filestats WHERE score>2 AND department='$userdepartement' GROUP BY Date

 

This will output eg: 1,2,5,0,0,2,0,4 ......

My problem is if no results are given for that day (NULL), I get output of '0', so I don't know if I hate multiple rows where score was less or equal to '2' or I had no rows at all at a specific Date.

Does this make more sense?

[Barand]

OK, my final guess at you scenario

INPUT...

TABLE: kilnakorr_date                   TABLE: kilnakorr
+------------+                          +----+----------+------------+-------+-------+------------+
| date       |                          | id | enne     | date       | score | user  | department |
+------------+                          +----+----------+------------+-------+-------+------------+
| 2019-10-01 |                          | 13 | ennehead | 2019-10-01 |     1 | fred  | sales      |
| 2019-10-02 |-----------+              | 14 | ennehead | 2019-10-01 |     3 | fred  | sales      |
| 2019-10-03 |           |              | 15 | ennehead | 2019-10-01 |     5 | fred  | sales      |
+------------+           |              | 16 | head     | 2019-10-01 |     1 | fred  | sales      |
                         |              | 17 | ennehead | 2019-10-01 |     3 | curly | accts      |
                         +------------0<| 18 | ennehead | 2019-10-01 |     5 | mo    | accts      |
                                        | 19 | ennehead | 2019-10-02 |     1 | fred  | sales      |
                                        | 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      |
                                        +----+----------+------------+-------+-------+------------+

Then...

SELECT
     d.date
   , CASE WHEN enne IS NULL THEN 'NO DATA'
          ELSE SUM(CASE WHEN `enne`='ennehead' AND score>2 AND user='fred' THEN 1 ELSE 0 END)
          END as head
FROM kilnakorr_date d 
       LEFT JOIN
     kilnakorr k  ON d.date = k.date
                  AND score>2 
                  AND department='sales' 
GROUP BY d.Date;
+------------+---------+
| date       | head    |
+------------+---------+
| 2019-10-01 | 2       |
| 2019-10-02 | 0       |
| 2019-10-03 | NO DATA |
+------------+---------+

 

[kilnakorr]

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

Edited October 27, 2019 by kilnakorr

[Barand]

Then we'll use sql magic to conjure the date table with a subquery

SELECT
     d.date
   , CASE WHEN enne IS NULL THEN 'NO DATA'
          ELSE SUM(CASE WHEN `enne`='ennehead' AND score>2 THEN 1 ELSE 0 END)
          END as head
FROM (
       SELECT DISTINCT date
       FROM kilnakorr
     ) d
       LEFT JOIN
     kilnakorr k  ON d.date = k.date
                  AND user='hercules' 
                  AND department='sales' 
GROUP BY d.Date;
+------------+---------+
| date       | head    |
+------------+---------+
| 2019-10-01 | NO DATA |
| 2019-10-02 | NO DATA |
| 2019-10-03 | NO DATA |
+------------+---------+

 

[kilnakorr]

Thanks a lot for your info.

I almost got it working (as mentioned, I only have on table, so no joining needed).

I'm still not getting the right output, as I have more conditions going that I can't really get my head around (seems I get 'NO DATA' output, if a row has other 'user' or 'enne' value, even though another row with same date exists with correct 'user and 'enne' value.

I'll fiddle around a bit, but might end up at the 'hire-someone-to-make-this-work' section

 

[Barand]

On 10/27/2019 at 12:23 PM, kilnakorr said:

It is vital that I get, a result for each distinct date

Then you definitely need a left join. Without it, if there is no data, there is no row.

Consider a teacher taking the morning attendance roll call. There are two way they can do it

• use a register of all the names and check off each child present
• request that anyone not present raise their hand

The former method is the LEFT JOIN approach; the latter method is what you are attempting

On 10/27/2019 at 12:23 PM, kilnakorr said:

and 'NO DATA'  if no rows are found matching  `enne` and  `user` for a date.

which suggests that the 'enne' condition should also be in the join's ON clause...

SELECT
     d.date
   , CASE WHEN enne IS NULL THEN 'NO DATA'
          ELSE SUM(score > 2)
          END as head
FROM (
       SELECT DISTINCT date
       FROM kilnakorr
     ) d
       LEFT JOIN
     kilnakorr k  ON d.date = k.date
                  AND enne = 'ennehead'
                  AND user='fred' 
                  AND department='sales' 
GROUP BY d.date;

EDIT:

Note that creating the data table using a subquery assumes that there will be at least one record for every date for someone. If this is not the case then you need to generate a (temporary) date table containing all the dates you need to appear, as in my earlier example.

Edited October 28, 2019 by Barand

[kilnakorr]

It seems I was overthinking it trying to add this to my existing code.

I did get it to work as intended!

Thank you so much for your help!