การสร้าง Query นับจำนวนกิจกรรมแต่ละเดือน/ปี

Code (PHP)
ผลที่ได้




แต่ถ้าต้องการให้แสดงผลเเบบนี้จะได้ไหมครับ



CODE

SELECT DISTINCT DATE_FORMAT( ACTIVITY_DATE, '%Y-%m' ) AS DISDATE, COUNT( ACTIVITY_ID ) AS Count

FROM activity

GROUP BY DATE_FORMAT( ACTIVITY_DATE, '%Y-%m' )

select
`Year`,
sum(`Jan`) as `Jan`,
sum(`Feb`) as `Feb`,
sum(`Mar`) as `Mar`,
sum(`Apr`) as `Apr`,
sum(`May`) as `May`,
sum(`Jun`) as `Jun`,
sum(`Jul`) as `Jul`,
sum(`Aug`) as `Aug`,
sum(`Sep`) as `Sep`,
sum(`Oct`) as `Oct`,
sum(`Nov`) as `Nov`,
sum(`Dec`) as `Dec`,
sum(`Total`) as `Total`
from
(select year(`ACTIVITY_DATE`) as `Year`,
case month(`ACTIVITY_DATE`) when 1 then count(`ACTIVITY_DATE`) else null end as `Jan`,
case month(`ACTIVITY_DATE`) when 2 then count(`ACTIVITY_DATE`) else null end as `Feb`,
case month(`ACTIVITY_DATE`) when 3 then count(`ACTIVITY_DATE`) else null end as `Mar`,
case month(`ACTIVITY_DATE`) when 4 then count(`ACTIVITY_DATE`) else null end as `Apr`,
case month(`ACTIVITY_DATE`) when 5 then count(`ACTIVITY_DATE`) else null end as `May`,
case month(`ACTIVITY_DATE`) when 6 then count(`ACTIVITY_DATE`) else null end as `Jun`,
case month(`ACTIVITY_DATE`) when 7 then count(`ACTIVITY_DATE`) else null end as `Jul`,
case month(`ACTIVITY_DATE`) when 8 then count(`ACTIVITY_DATE`) else null end as `Aug`,
case month(`ACTIVITY_DATE`) when 9 then count(`ACTIVITY_DATE`) else null end as `Sep`,
case month(`ACTIVITY_DATE`) when 10 then count(`ACTIVITY_DATE`) else null end as `Oct`,
case month(`ACTIVITY_DATE`) when 11 then count(`ACTIVITY_DATE`) else null end as `Nov`,
case month(`ACTIVITY_DATE`) when 12 then count(`ACTIVITY_DATE`) else null end as `Dec`,
count(`ACTIVITY_DATE`) as Total
from activity group by year(`activity_date`), month(`activity_date`))
as temp group by `Year`

 

ผลล้พธ์