CODEWITHSUNDEEP
mysql
J S
J S

Reputation: 41

How to get rows for each month separately in mysql

I have a table as follows

CREATE TABLE IF NOT EXISTS `titas_clp_invoice_master` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) NOT NULL,
  `store_id` bigint(20) NOT NULL,
  `invoice_no` varchar(255) NOT NULL,
  `invoice_date` date NOT NULL,
  `amount` bigint(255) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `isdeleted` varchar(1) NOT NULL,
  `invoice_month` varchar(255) NOT NULL,
  `invoice_year` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

Now I want something like this

Total transactions for each month of an year separately. Like 1 transaction for the Month of December 2015, 2 transactions for the month of January 2016, 3 transactions for the month of February 2016 etc.

Upvotes: 2

Views: 65

Answers (1)

baao
baao

Reputation: 73301

MySQL's developers took quite some time to develop great date functions - you should use them. There is no need for both invoice_year nor invoice_month as you already have invoice_date as a date formatted column. You can just use

SELECT invoice_date, count(*) 
FROM titas_clp_invoice_master
GROUP BY YEAR(invoice_date), MONTH(invoice_date)

Upvotes: 1

Related Questions