mysql - sql case when where limit 1

Question

I am confused about why this happened?

select case when COUNT(1)> 0
           then remain_salary_money
           else 0
       end AS remainBorrowMoney
from  salary_pay_detail
where project_id = 483984364271566848 
    and team_id = 491297935619784704
    and DATE(CONCAT(`year`,'-',`month`,'-01')) < '2020-11-01'
ORDER BY create_time desc
LIMIT 1

and another sql

select remain_salary_money
from salary_pay_detail
where project_id = 483984364271566848 
    and team_id = 491297935619784704
    and DATE(CONCAT(`year`,'-',`month`,'-01')) < '2020-11-01'
ORDER BY create_time desc
LIMIT 1

I got 1948484 in the first sql result, 339220 is the second sql result .

you can image two records:

| month | remain_salary_money   | year | create_time|
| ----- | --------------------  | ---- | ---------- |
| 09    | 1948484               | 2020 | 2021-01-07 19:45:20|
| 10    | 339220                | 2020 | 2021-01-08 19:45:25 |

I want to know what happed ?

this is for test

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `month` int DEFAULT NULL,
  `year` int DEFAULT NULL,
  `money` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

insert sql

INSERT INTO `test`(`id`, `month`, `year`, `money`, `create_time`) VALUES (1, 9, 2020, 1111, '2021-01-07 19:45:20');
INSERT INTO `test`(`id`, `month`, `year`, `money`, `create_time`) VALUES (2, 10, 2020, 2222, '2021-01-08 19:45:25');

test sql

select  money from test where DATE(CONCAT(`year`,'-',`month`,'-01')) < '2020-11-01'
ORDER BY create_time desc
LIMIT 1;

select  case when COUNT(1)> 0
           then money
           else 0  end from test where DATE(CONCAT(`year`,'-',`month`,'-01')) < '2020-11-01'
ORDER BY create_time desc
LIMIT 1;

Apparently,I am wondering why select case when count(1)>0... can not return 339220

question from:https://stackoverflow.com/questions/65626906/sql-case-when-where-limit-1

Answer 1

An aggregate function without a group by is over the whole set. What your query with the count returns is indeterminate..

Add in the fact that you are filtering by date and month but ordering(inconsistently) by createdtime then I cannot figure out what you are trying to do.

What were you trying to achieve?