MySQL counting instances of column values and sorting by column value

Question

I have a rather advanced query I need to do on a table that holds trouble tickets sent in by customers, to gather statistics about the tickets, and to order and sort by various counts and/or column values.

Tickets table:

+---------------------------+       
| product_id | issue        |
+------------|--------------+
| 1          | missing-part |
| 1          | missing-part |
| 1          | broken-part  |
| 2          | broken-part  |
| 2          | broken-part  |
| 2          | missing-part |
| 2          | missing-part |
| 2          | missing-part |
+---------------------------+

First, I need to count the total number of tickets per product. In this case, product_id 1 has 3 tickets and product_id 2 has 5 tickets. Then, I need to get the values of the issue column and the number of occurrences of each. So at this point, I'm expecting something like this:

[
  'product_id' => 1,
  'issues'     => 3
  'issue_nums' => [
    'missing-part' => 2,
    'broken-part'  => 1
  ]
],
[
  'product_id' => 2,
  'issues'     => 5,
  'issue_nums' => [
    'missing-part' => 3,
    'broken-part'  => 2
  ]
]

Then, I need to be able to order by the number of of occurrences of each issue, for example: ORDER BY COUNT(missing-part) or COUNT(broken-part). In other cases, I just need to be able to order the products by number of issues in total rather than by the issue values themselves (so ordering by COUNT(issues)).

Thanks!

You are expected to try to write the code yourself. After doing more research if you have a problem post what you've tried with a clear explanation of what isn't working and provide a Minimal, Complete, and Verifiable example. Read How to Ask a good question. Be sure to take the tour and read this. — Jay Blanchard
– Jay Blanchard, Commented Jul 11, 2017 at 16:44
@JayBlanchard added an SQL Fiddle to the question that demonstrates where the query is at so far, and edited the question to clarify what additions the query needs. — Chris Clower
– Chris Clower, Commented Jul 11, 2017 at 19:23

Gordon Linoff · Accepted Answer · 2017-07-11 17:59:19Z

1

You can come pretty close with two levels of aggregation:

select t.product_id, sum(cnt) as total,
       group_concat(issue, ':', cnt separator ',') as issues
from (select t.product_id, t.issue, count(*) as cnt
      from tickets t
      group by t.product_id, t.issue
     ) tp
group by t.product_id;

answered Jul 11, 2017 at 17:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Clower Over a year ago

Here's a SQL Fiddle of your example: sqlfiddle.com/#!9/323bbb/32. It has almost everything, except being able to order the results by issue occurrence. For example: which products have the most missing-parts or broken-parts?

Chris Clower Over a year ago

I went ahead and accepted your answer and moved the second half of the question to a new question to avoid confusion. stackoverflow.com/questions/45044970/…

ScaisEdge · Accepted Answer · 2017-07-11 16:54:04Z

1

yu could use a subselect for count product_id occurence and a main select for issuse

  select t1.product_id, t2.product_count, t1.issue, count(*) count_issue
  from  Tickets t1
  inner join  (
  select product_id, count(*)  product_count
  from Tickets 
  group by product_id) t2 on t2.product_id = t1.product_id
  group by t1.product_id, t2.product_count, t1.issue
  order by  count_issue

answered Jul 11, 2017 at 16:54

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

2 Comments

Chris Clower Over a year ago

Thanks for your answer. I've added an SQL Fiddle example to the question that demonstrates where it's at so far. Basically the data formatted there is exactly how it needs to be, except for the Issues column, which should list out the issues submitted for the product, and the number of times that issue occurs. Then, I need to also be able to sort by number of issue occurrences, so for example highest->lowest number of missing-parts. Does that make sense?

ScaisEdge Over a year ago

t1.issue and count_issue should return then isseus submitted and the related occurring time .. and order by coun_issue shoudl return the list order by number of occurrence ... ..

Collectives™ on Stack Overflow

MySQL counting instances of column values and sorting by column value

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related