Jump to content
kilnakorr

Outputting chosen string instead of zero?

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
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 by Barand

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.