CODEWITHSUNDEEP
mysqlsql-server
Kriz
Kriz

Reputation: 357

SQL query to generate count for every month in a year

I need to generate a count of transactions for every month in an year. I have the query below:

select state, taxing_entity, count(taxing_entity)Total_TRXN_Count
from taxes where effect_date between '2015/01/01' and '2015/12/31' 
group by state, taxing_entity, effect_date

I have tried various ways like GROUP BY YEAR(record_date), MONTH(record_date) and GROUP BY DATE_FORMAT(record_date, '%Y%m'). How can I change the above query so as to generate a count of transactions for every month in a year without manually changing the year once it ends

Upvotes: 1

Views: 478

Answers (3)

Zach Victor
Zach Victor

Reputation: 469

You do not need the WHERE clause, because the aggregation will automatically separate the data by year:

SELECT state
     , taxing_entity
     , DATE_FORMAT(record_date, '%Y-%m') YearMonth
     , COUNT(taxing_entity) AS Total_TRXN_Count
  FROM taxes 
 GROUP BY state
        , taxing_entity
        , DATE_FORMAT(record_date, '%Y-%m')
;

I chose to aggregate by DATE_FORMAT(record_date, '%Y-%m').

If you prefer, you could also aggregate by year and month in separate columns: YEAR(record_date), MONTH(record_date)

If you are importing data into Microsoft Excel, then I recommend "truncating" the date to the first of the month and then changing formatting in Excel to display only the Month and Year: DATE_FORMAT(record_date, '%Y-%m-01')

Upvotes: 0

Keith Barrows
Keith Barrows

Reputation: 25308

select MONTH(effect_date) Reporting_Month
     , state
     , taxing_entity
     , count(taxing_entity) Total_TRXN_Count
  from taxes 
 where effect_date between '2015/01/01' and '2015/12/31' 
 group by MONTH(effect_date)
     , state
     , taxing_entity
 order by MONTH(effect_date)
     , state
     , taxing_entity

Does that do what you are looking for? It should give totals by month for each state and taxing_entity.

Upvotes: 2

Squirrel
Squirrel

Reputation: 24803

select state, taxing_entity, 
       YEAR(effect_date), MONTH(effect_date),
       count(taxing_entity) AS Total_TRXN_Count
from  taxes 
where effect_date between '2015/01/01' and '2015/12/31' 
group by state, taxing_entity, YEAR(effect_date), MONTH(effect_date)

Upvotes: 0

Related Questions