below data present in database.
startingdate - varchar
tablename - couponentry
coupon price month startingdate 100 15 1-month 02-03-2015 101 15 1-month 04-03-2015 102 15 1-month 05-03-2015 103 15 1-month 07-04-2015 104 15 1-month 08-04-2015 105 15 1-month 15-05-2015 106 15 1-month 18-05-2015 107 15 2-month 02-02-2015 108 15 2-month 04-02-2015 109 15 2-month 05-02-2015 110 15 2-month 07-03-2015 111 15 2-month 08-03-2015 112 15 2-month 15-05-2015 113 15 2-month 18-05-2015 114 15 2-month 18-05-2015 i need count of total coupon based on 1-month,2-month , on..and need display month wise seprate totalcount of coupon.
for ex - in above data - total 7 coupon created in 1-month. 3 coupon comes in march,2 in april , 2 in may month.... wise...
expected output below
month jan feb mar april may june ... totalcoupon totalprice 1-month 3 2 2 7 105 2-month 3 2 3 8 120 below query tried...
select month,count(ce.coupon) total,sum(ce.price) totalprice coupon_entry ce left join subagentmaster sam on ce.subagentid = sam.id left join tehsilmaster tm on sam.tehsil = tm.id left join city_master cm on ce.city = cm.id cm.cityname = 'nagpur' group ce.month but getting below output
month totalcoupon totalprice 1-month 7 105 2-month 8 120
below query :
select month , sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 1, 1, 0)) jan, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 2, 1, 0)) feb, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 3, 1, 0)) mar, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 4, 1, 0)) apr, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 5, 1, 0)) may, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 6, 1, 0)) jun, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 7, 1, 0)) jul, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 8, 1, 0)) aug, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 9, 1, 0)) sep, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 10, 1, 0)) oct, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 11, 1, 0)) nov, sum(if(month(str_to_date(startingdate,'%d-%m-%y')) = 12, 1, 0)) `dec` ,count(*) totalcoupon ,sum(price) totalprice mytable group month ; 
Comments
Post a Comment