CODEWITHSUNDEEP
mysql
ankit ingle
ankit ingle

Reputation: 213

sql query to get month wise count

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

Upvotes: 2

Views: 5674

Answers (2)

Vishvesh Phadnis
Vishvesh Phadnis

Reputation: 2598

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     ;

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

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.

Upvotes: 0

Related Questions