harika harika - 1 month ago 21
MySQL Question

working in mysql but not in db2. SQL State: 42601] DB2 SQL Error: SQLSTATE=42601, SQLERRMC==;sum(day(p.create_dt);CONCAT

Below is my query

select YEAR(p.create_dt) as year,
month(p.create_dt) as month,
p.system as systemName,
p.status as status,
sum(day(p.create_dt) = '01') as count1,
sum(day(p.create_dt) = '02') as count2
from pnm_history p
group by year(p.create_dt),
month(p.create_dt)
order by year(p.create_dt),
month(p.create_dt);


getting error near sum()

Answer

Replace the day() calls in MySQL with CASE statements:

SELECT p2.year,
       p2.month,
       p1.system AS systemName,
       p1.status AS status,
       p2.count1,
       p2.count2
FROM pnm_history p1
INNER JOIN
(
    SELECT YEAR(p.create_dt) AS year,
           MONTH(p.create_dt) AS month,
           SUM(CASE WHEN DAY(p.create_dt) = 1 THEN 1 ELSE 0 END) AS count1,
           SUM(CASE WHEN DAY(p.create_dt) = 2 THEN 1 ELSE 0 END) AS count2
    FROM pnm_history p
    GROUP BY YEAR(p.create_dt),
             MONTH(p.create_dt)
) p2
    ON YEAR(p1.create_dt)  = p2.year AND
       MONTH(p1.create_dt) = p2.month
ORDER BY p2.year,
         p2.month