i've problem with sql. here's my sql code :

SELECT COUNT(id) as jml_bulan
        case when jml_bulan > 0 then jml_bulan else 0 end
        FROM table_so_sales
        WHERE(branch_id_so = '.$value->id.')    
        
        GROUP BY DATE_FORMAT(created_at, "%Y%m")

as you can see, i try to fetch how many id based on year and month. the problem is, if there is no count(id) at that month (for example january) it won't be 0. instead it will go to next month where the count(id) is > 0. so I think i must add some condition. i've tried to follow this similar problem ( i think) MySql: is it possible to 'SUM IF' or to 'COUNT IF'? but it doesn't work. here's the error

you have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select(case when jml_bulan > 0 then 1 else 0 end) FROM table_so_...'

thank you in advance

===== edit =====

everyone telling me that I need to add comma, but when I add it, the error turn into "Unknown column 'jml_bulan' in 'field list'"

CodePudding user response：

In syntax your request must be preceded by a comma to resolve the issue:

SELECT COUNT (id) as jml_bulan,
case 
    when jml_bulan> 0 then jml_bulan
    else 0
end
FROM table_so_sales
WHERE (branch_id_so = '. $ Value-> id.')
GROUP BY DATE_FORMAT (created_at, "% Y% m");

CodePudding user response：

First, your error caused by you are missing a comma after jml_bulan column from select

From your question, I think you might use a calendar table with outer-join to count with your expectation.

Query #1

SELECT COUNT(id) as jml_bulan,
       t1.dt
FROM (
   SELECT '202001' dt
   UNION ALL 
   SELECT '202002' 
   UNION ALL 
   SELECT '202003' 
   UNION ALL 
   SELECT '202004' 
   UNION ALL 
   SELECT '202005' 
) t1
LEFT JOIN table_so_sales
ON DATE_FORMAT(created_at, "%Y%m") = t1.dt
WHERE(branch_id_so = '.$value->id.') 
GROUP BY t1.dt;

View on DB Fiddle

if your MySQL version support CTE recursive, you can try to use CTE recursive to create a calendar date table then do outer-join

Query #1

with recursive cte  as (
  select     cast('2020-01-01' as date) startdt,
             cast('2021-01-01' as date)   enddt
  union all
  select     DATE_ADD(startdt, INTERVAL  1 MONTH),
             enddt
  from       cte 
  where DATE_ADD(startdt, INTERVAL  1 MONTH) <= enddt
)
select  COUNT(id) as jml_bulan,
        DATE_FORMAT(startdt, "%Y%m") 
from cte
LEFT JOIN table_so_sales
ON DATE_FORMAT(created_at, "%Y%m") = DATE_FORMAT(startdt, "%Y%m")
WHERE(branch_id_so = '.$value->id.') 
GROUP BY  DATE_FORMAT(startdt, "%Y%m")
ORDER BY  DATE_FORMAT(startdt, "%Y%m");

View on DB Fiddle

CodePudding user response：

You could use use a SUM based on proper case condition

SELECT  DATE_FORMAT(created_at, "%Y%m")
   , sum(case when ifnull(jml_bulan,0) > 0 then 1 else 0 end) as jml_bulan
FROM table_so_sales
WHERE(branch_id_so = '.$value->id.')    
GROUP BY DATE_FORMAT(created_at, "%Y%m")