sql query to get month wise count

Question

below is my data present in my 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 and so on..and also need to display month wise seprate totalcount of coupon.

FOR EX - In above data - total 7 coupon created in 1-month. from this 3 coupon comes in march,2 in april and 2 in may month.... like wise...

expected output like 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 is my query which i tried...

SELECT month,COUNT(CE.coupon) As Total,SUM(CE.Price) AS TotalPrice FROM 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 WHERE CM.cityname = 'nagpur'    GROUP BY CE.month

but this is getting below output

month       totalcoupon     totalprice
1-month        7                 105
2-month        8                 120

yes i tried mid or substr function to extract month but not getting output as i want — ankit ingle
– ankit ingle, Commented May 8, 2015 at 12:32
i only need to break total couponvalue month wise...in my query..thats it... for ex- in 1-month there are total 7 coupon ...i also need to display month wise count of this 7..for ex - in march = 3,april - 2,may - 2 like wise... — ankit ingle
– ankit ingle, Commented May 8, 2015 at 12:36

Vishvesh Phadnis · Accepted Answer · 2015-05-08 12:54:38Z

Below Query will help you :

SELECT
  MONTH     ,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 1, 1, 0))  AS Jan,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 2, 1, 0))  AS Feb,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 3, 1, 0))  AS Mar,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 4, 1, 0))  AS Apr,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 5, 1, 0))  AS May,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 6, 1, 0))  AS Jun,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 7, 1, 0))  AS Jul,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 8, 1, 0))  AS Aug,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 9, 1, 0))  AS Sep,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 10, 1, 0)) AS OCT,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 11, 1, 0)) AS Nov,
  SUM(IF(MONTH(STR_TO_DATE(startingdate,'%d-%m-%Y')) = 12, 1, 0)) AS `Dec`
  ,COUNT(*) totalCoupon
  ,SUM(price) totalprice

FROM mytable
GROUP BY MONTH     ;

Strawberry · Accepted Answer · 2015-05-08 12:36:03Z

0

Ignoring the whole nagpur bit...

SELECT month
     , SUM(CASE WHEN startingdate >= '2015-01-01' AND startingdate < '2015-02-01' THEN 1 ELSE 0 END) 'jan' 
     , SUM(CASE WHEN startingdate >= '2015-02-01' AND startingdate < '2015-03-01' THEN 1 ELSE 0 END) 'feb' 
     , SUM(CASE WHEN startingdate >= '2015-03-01' AND startingdate < '2015-04-01' THEN 1 ELSE 0 END) 'mar' 
     , SUM(CASE WHEN startingdate >= '2015-04-01' AND startingdate < '2015-05-01' THEN 1 ELSE 0 END) 'apr' 
     , SUM(CASE WHEN startingdate >= '2015-05-01' AND startingdate < '2015-06-01' THEN 1 ELSE 0 END) 'may' 
     , SUM(CASE WHEN startingdate >= '2015-06-01' AND startingdate < '2015-07-01' THEN 1 ELSE 0 END) 'jun' 
     , SUM(CASE WHEN startingdate >= '2015-07-01' AND startingdate < '2015-08-01' THEN 1 ELSE 0 END) 'jul' 
     , SUM(CASE WHEN startingdate >= '2015-08-01' AND startingdate < '2015-09-01' THEN 1 ELSE 0 END) 'aug' 
     , SUM(CASE WHEN startingdate >= '2015-09-01' AND startingdate < '2015-10-01' THEN 1 ELSE 0 END) 'sep' 
     , SUM(CASE WHEN startingdate >= '2015-10-01' AND startingdate < '2015-11-01' THEN 1 ELSE 0 END) 'oct' 
     , SUM(CASE WHEN startingdate >= '2015-11-01' AND startingdate < '2015-12-01' THEN 1 ELSE 0 END) 'nov' 
     , SUM(CASE WHEN startingdate >= '2015-12-01' AND startingdate < '2016-01-01' THEN 1 ELSE 0 END) 'dec'
     , COUNT(*) totalcoupon
     , SUM(price) totalprice
  FROM couponentry
 GROUP 
    BY month;

However, as you mention xampp - it would be simpler (and more scalable) to do this in PHP.

answered May 8, 2015 at 12:36

Strawberry

34k14 gold badges43 silver badges57 bronze badges

2 Comments

Strawberry Over a year ago

Yes. Fix your dates. Then try again.

ankit ingle Over a year ago

yes i tried but how to make this startingdate dynamic bcoz my table has more than 50,000 rows and also different startingdate .. for ex- year may be any like 2014-05-02,2013-10-05, 2015-03-02 like wise...

Collectives™ on Stack Overflow

sql query to get month wise count

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related