final:- SELECT tb.`campaign_key`, tb.firstname, tb.lastname, tb.referenceId, tb.did, SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 01 THEN (msg_count) ELSE 0 END) AS '1 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 02 THEN (msg_count) ELSE 0 END) AS '2 Nov' , SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 03 THEN (msg_count) ELSE 0 END) AS '3 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 04 THEN (msg_count) ELSE 0 END) AS '4 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 05 THEN (msg_count) ELSE 0 END) AS '5 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 06 THEN (msg_count) ELSE 0 END) AS '6 Nov' , SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 07 THEN (msg_count) ELSE 0 END) AS '7 Nov' , SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 08 THEN (msg_count) ELSE 0 END) AS '8 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 09 THEN (msg_count) ELSE 0 END) AS '9 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 10 THEN (msg_count) ELSE 0 END) AS '10 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 11 THEN (msg_count) ELSE 0 END) AS '11 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 12 THEN (msg_count) ELSE 0 END) AS '12 Nov' , SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 13 THEN (msg_count) ELSE 0 END) AS '13 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 14 THEN (msg_count) ELSE 0 END) AS '14 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 15 THEN (msg_count) ELSE 0 END) AS '15 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 16 THEN (msg_count) ELSE 0 END) AS '16 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 17 THEN (msg_count) ELSE 0 END) AS '17 Nov' , SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 18 THEN (msg_count) ELSE 0 END) AS '18 Nov' , SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 19 THEN (msg_count) ELSE 0 END) AS '19 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 20 THEN (msg_count) ELSE 0 END) AS '20 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 21 THEN (msg_count) ELSE 0 END) AS '21 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 22 THEN (msg_count) ELSE 0 END) AS '22 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 23 THEN (msg_count) ELSE 0 END) AS '23 Nov' , SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 24 THEN (msg_count) ELSE 0 END) AS '24 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 25 THEN (msg_count) ELSE 0 END) AS '25 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 26 THEN (msg_count) ELSE 0 END) AS '26 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 27 THEN (msg_count) ELSE 0 END) AS '27 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 28 THEN (msg_count) ELSE 0 END) AS '28 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 29 THEN (msg_count) ELSE 0 END) AS '29 Nov', SUM(CASE WHEN DAY(message_accepted_by_signalmash) = 30 THEN (msg_count) ELSE 0 END) AS '30 Nov' FROM (SELECT tbl.`campaign_key`, tbl.firstname, tbl.lastname, tbl.referenceId, d.did FROM (SELECT c.`campaign_key`, b.firstname,b.lastname,b.referenceId FROM (SELECT * FROM brand WHERE entityType='SOLE_PROPRIETOR') b LEFT JOIN (SELECT idbrand, campaign_key FROM campaign GROUP BY campaign_key) c ON b.brandId=c.idbrand WHERE campaign_key IS NOT NULL) tbl LEFT JOIN did_buy d ON tbl.`campaign_key`=d.`camp_registry`) tb RIGHT JOIN mdr ON mdr.from_number=tb.did WHERE MONTHNAME(message_accepted_by_signalmash)='november' AND mdr.direction='outbound' AND mdr.mobility LIKE 'T%' GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d'), tb.referenceId
create view of this query in mysql????