Why i am not getting proper output while create pivot table using mysql-CodePudding

i am trying to get record based on on date range and get data in pivot table format but maybe something wrong, only one day data getting less day record not coming

MySQL QUERY-

SELECT number,
if(date= '2022-08-22', status,  null) day1_status,
if(date= '2022-08-23', status,  null) day2_status,
if(date= '2022-08-24', status,  null) day3_status,
if(date= '2022-08-25', status,  null) day4_status
from table where date BETWEEN '2022-08-22' and '2022-08-25'
GROUP by number

output -

number	day1_status	day2_status	day3_status	day4_status
10	active	null	null	null
20	active	null	null	null

above mention output is wrong my expected output is-

number	day1_status	day2_status	day3_status	day4_status
10	active	no	active	no
20	active	active	no	active

Current mysql table.

id	number	status	date
1	10	active	2022-08-22
2	10	no	2022-08-23
3	10	active	2022-08-24
4	10	no	2022-08-25
5	20	active	2022-08-22
6	20	active	2022-08-23
7	20	no	2022-08-24
8	20	active	2022-08-25

CodePudding user response：

You are using GROUP BY without an aggregate function.

SELECT 
  number,
  max(if(`date`= '2022-08-22', status,  null)) day1_status,
  max(if(`date`= '2022-08-23', status,  null)) day1_status,
  max(if(`date`= '2022-08-24', status,  null)) day1_status,
  max(if(`date`= '2022-08-25', status,  null)) day1_status
from `table` where date BETWEEN '2022-08-22' and '2022-08-25'
GROUP by number

See dbfiddle