Mysql - Merge rows with unique value - group and count them

Question

I have some trouble getting my query right for a specific output.

My table looks like this:

ID | meta_value | field_id | item_id
------------------------------------
1  | Steve      | 75       | 5
2  | Johnsson   | 76       | 5
3  | Sick       | 705      | 5
4  | John       | 75       | 6
5  | Doe        | 76       | 6
6  | Sick       | 705      | 6
7  | Laura      | 75       | 7
8  | Jenner     | 76       | 7
9  | Sick       | 705      | 7
10 | Laura      | 75       | 8
11 | Jenner     | 76       | 8
12 | Vacation   | 705      | 8
13 | Steve      | 75       | 9
14 | Johnsson   | 76       | 9
15 | Sick       | 705      | 9

And I want to merge - group by item_id and their combined meta_value - and count the results, where the value is "Sick" - Order by count, as follows:

Name:               Sick (Count):
Steve Johnsson      2
John Doe            1
Laura Jenner        1

(Vacation is left out)

I think I've tried all possible combination, but obviously nothing seems to be right. (Changing the table is not an option). I've been trying for hours...

Please help :)

Thanks in advance!

What have you tried? Did you try to use 'GROUP_CONCAT'? Also, I don't understand why Steve Johnsson is 2 and John Doe is 1. — clinomaniac
– clinomaniac, Commented Mar 12, 2018 at 21:28

Gordon Linoff · Accepted Answer · 2018-03-13 01:45:22Z

1

Try two levels of aggregation:

select first_name, last_name, count(*)
from (select max(case when field_id = 75 then meta_value end) as first_name,
             max(case when field_id = 76 then meta_value end) as last_name,
             max(case when field_id = 705 then meta_value end) as reason
      from t
      group by item_id
     ) t
where reason = 'sick'
group by first_name, last_name
order by count(*) desc;

edited Mar 13, 2018 at 1:45

answered Mar 12, 2018 at 21:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Thorsten Kettner Over a year ago

Much more elegant than my query.

Thorsten Kettner · Accepted Answer · 2018-03-12 21:44:52Z

0

Key value tables are ugly, but usually they have at least a grouping column. Yours doesn't. You must find out first which item_ids represent the same user by looking up the names. (And hoping there aren't two different John Smith in the table.)

You'd aggregate per user_id first, then aggregate again per name:

select
  name,
  sum(item_sick_count) as sick_count
from
(
  select
    concat(
      any_value(case when field_id = 75 then meta_value),
      ' ',
      any_value(case when field_id = 76 then meta_value)
    ) as name,
    sum(field_id = 705 and meta_value = 'Sick') as item_sick_count
  from mytable
  group by item_id
)
group by name
order by sick_count desc, name;

The sick_count formula makes use of MySQL's true = 1, false = 0.

edited Mar 12, 2018 at 21:44

answered Mar 12, 2018 at 21:32

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

1 Comment

Evert Over a year ago

This is a good demonstration on why key->value tables are probably a bad idea.

Paul Spiegel · Accepted Answer · 2018-03-12 21:52:42Z

0

Join the table with itself once for every field:

select
  f.meta_value as first_name,
  l.meta_value as last_name,
  count(*) as sick_count
from eav s
join eav f using(item_id)
join eav l using(item_id)
where s.field_id = 705
  and s.meta_value = 'Sick'
  and f.field_id = 75
  and l.field_id = 76
group by first_name, last_name
order by sick_count desc

answered Mar 12, 2018 at 21:52

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Collectives™ on Stack Overflow

Mysql - Merge rows with unique value - group and count them

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related