Number of Issues per custom field

[Sanket Mane]

I am looking for a Database query which gives me number of issues associated per custom field within JIRA. 

[Nic Brough -Adaptavist-]

You will find it a lot easier to do this with JQL.

Run a query for "custom field is not empty".  It'll give you the number of issues, and normalised for multi-option fields.

[Sanket Mane]

Our instance of JIRA has more than 2000 custom fields. So, doing it manually would be a big task. Any quick solution for it?

[Nic Brough -Adaptavist-]

Mmm, some will tell you to reach for SQL, but it's only going to do some of the work.

For single-option fields, you can get somewhere with

select count(customfield) from customfieldvalue group by (customfield)

But I'd only use that to identify low usage fields.  It won't help much with multi-option fields, and won't tell you anything about zero usage. 

I'd use that to clean up as many low-use fields as you can, and list high-usage, then you can use "customfield is empty" to check anything you think might be unused.

[Sanket Mane]

When you say single-option fields do you mean single list (single choice) fields?
Your query is not working- can you please elaborate on it. 

[Nic Brough -Adaptavist-]

The SQL is a generic query that needs to be adjusted to whatever database you are using, and it's from memory, so I'm not 100% sure the customfield column name is right.

Single-option fields are the ones that have only one entry per issue, so text, dates, single-select, radio buttons etc.  Multi fields are the ones SQL aren't going to be good for - multi-select, labels, check boxes etc.

[Sanket Mane]

Database which we are using is MySQL