CODEWITHSUNDEEP
mysqlsqlselectcase
David Hermansson
David Hermansson

Reputation: 45

Trouble with SELECT CASE

I want to get some data from my database for the past 31 days. When column 'finalized' is NULL I want to get data from column 'today', but if column 'finalized' isn't NULL I want to get data from column 'finalized'. I always want to get the last entered row.

TABLE MyEarnings

id          INT(11) AI
date        datetime
today       decimal(4,2)    NULL
finalized   decimal(4,2)    NULL

id    date                   today    finalized
-----------------------------------------------
6     2016-02-04 04:52:00    0.39     NULL
5     2016-02-03 12:34:00    NULL     19.74
4     2016-02-03 12:33:00    15.96    NULL
3     2016-02-03 12:32:00    12.32    NULL
2     2016-02-02 15:16:00    NULL     9.16
1     2016-02-02 14:29:00    2.20     NULL

SQL

SELECT
    date,
    CASE
        WHEN finalized=NULL
            THEN today
        WHEN finalized!=NULL
            THEN finalized
        END
    AS earn
FROM MyEarnings
GROUP BY DATE_FORMAT(date, '%Y-%m-%d')
ORDER BY date  ASC
LIMIT 0 , 31

This is what I end up with

date                   earn
---------------------------
2016-02-02 00:00:00    NULL
2016-02-03 00:00:00    NULL
2016-02-04 00:00:00    NULL

What I'm looking to get

date                   earn
----------------------------
2016-02-02 00:00:00    9.16
2016-02-03 00:00:00    19.74
2016-02-04 00:00:00    0.39

EDIT

I also want to get a summary for each month of all values in 'finalized'-column with the max 'id' for each day.

Upvotes: 1

Views: 70

Answers (4)

Arth
Arth

Reputation: 13110

Using greatest id to determine last row added (cons: stretching traditional use of id):

SELECT DATE(me.date) date,
       COALESCE(me.finalized,me.today) earn
  FROM MyEarnings me 
  JOIN (
    SELECT MAX(id) max_id
      FROM MyEarnings
  GROUP BY DATE(date)
  ORDER BY DATE(date)
     LIMIT 31 
      ) o
    ON o.max_id = me.id

Using greatest date to determine last row added (cons: possible dupes):

SELECT DATE(me.date) date,
       COALESCE(me.finalized,me.today) earn
  FROM MyEarnings me 
  JOIN (
    SELECT MAX(date) max_date
      FROM MyEarnings
  GROUP BY DATE(date)
  ORDER BY DATE(date)
     LIMIT 31 
      ) o
    ON o.max_date = me.date

Upvotes: 0

Pathik Vejani
Pathik Vejani

Reputation: 4491

use CASE WHEN like this:

CASE
        WHEN finalized IS NULL
            THEN today
        ELSE finalized
        END
AS earn

Replace query:

SELECT date, (CASE
                  WHEN finalized IS NULL THEN today
                  ELSE finalized
              END;    
) AS earn
FROM MyEarnings
GROUP BY DATE_FORMAT(date, '%Y-%m-%d')
ORDER BY date ASC LIMIT 0,31

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269573

You need conditional aggregation, but it is a bit tricky. I think this does what you want:

SELECT DATE_FORMAT(date, '%Y-%m-%d'),
       COALESCE(MAX(finalized), MAX(today)) as earn
FROM MyEarnings
GROUP BY DATE_FORMAT(date, '%Y-%m-%d')
ORDER BY date  ASC
LIMIT 0 , 31;

This returns the maximum value of today. You may want the most recent value. If so, the simplest method is probably the GROUP_CONCAT()/SUBSTRING_INDEX() method:

SELECT DATE_FORMAT(date, '%Y-%m-%d'),
       COALESCE(MAX(finalized),
                SUBSTRING_INDEX(GROUP_CONCAT(today ORDER BY date DESC), ',', 1) + 0
               ) as earn
FROM MyEarnings
GROUP BY DATE_FORMAT(date, '%Y-%m-%d')
ORDER BY date  ASC
LIMIT 0 , 31;

It is a bit yucky to convert numbers to strings and back for this purpose. Alternative methods require additional joins or using variables.

Upvotes: 1

sagi
sagi

Reputation: 40481

You can't compare null with !=, you should use is null and is not null like this:

SELECT
date,
CASE
    WHEN finalized is null THEN today
    ELSE finalized
    END AS earn
FROM MyEarnings
GROUP BY DATE_FORMAT(date, '%Y-%m-%d')
ORDER BY date  ASC  LIMIT 0 , 31

Also, if your first condition is when its null, you don't need to check if its not null , ELSE will be enough since the opposite of null is not null

Upvotes: 2

Related Questions