-4

I am trying to get my list of counts into a single row result set.

Consider this simple data set:

GRADES
=====================
ID   NAME   MIN   MAX
1    A      91    100
2    B      81    90
3    C      71    80
=====================

Units
=====================
ID    NAME
1     Unit 1
2     Unit 2
=====================

TESTS
=====================
ID   UNIT_ID   SCORE
1    1         80
2    1         92
3    1         95
4    1         94
5    2         92
6    2         85
7    2         76
8    2         73
======================

I want to get the result set to be something like this:

Unit_Name   Grades    Counts
Unit 1      A,B,C     3,1,0
Unit 2      A,B,C     2,1,1

Or even better, a JSON object:

Unit_Name   Grade_Counts
Unit 1      {A: 3, B: 1, C: 0}
Unit 2      {A: 2, B: 1, C: 1}

I am easily able to create the grades and counts, but I end up with this, which doesn't match what I'm trying to achieve above.

UNIT_NAME   Grade   Count
Unit 1      A       3
Unit 1      B       1
Unit 1      C       0
Unit 2      A       2
Unit 2      B       1
Unit 2      C       1

UPDATE:

Here is the simple query I'm using, and I cannot use any of the GROUP_CONCAT or the json_objectagg function against the count field.

SELECT units.name as unit_name, grades.name as grade, count(grades.name) as count
FROM units
join tests on tests.unit_id = units.id
join grades on tests.score BETWEEN grades.min and grades.max
group by units.id, grades.name
order by units.id;
Ben Midget
  • 1
  • 2
  • Once you produced the grades and counts, you can GROUP BY unit_name and use the GROUP_CONCAT() function – dcolazin Feb 20 '22 at 16:27
  • Does this answer your question? [How to use GROUP BY to concatenate strings in MySQL?](https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) – Stu Feb 20 '22 at 16:28
  • Show the query which produces grades and counts and you have no `Unit 3` in your data examples , why is it in the expected result ? – Ergest Basha Feb 20 '22 at 17:02
  • @dcolazin the GROUP_CONCAT function won't work against my `count` field – Ben Midget Feb 20 '22 at 17:25
  • @ErgestBasha I've added the query and updated the data example eliminating my typos. – Ben Midget Feb 20 '22 at 17:26

1 Answers1

1

Try:

select unit_name,group_concat(grade) as Grades ,group_concat(nr_count)  as Counts  
 from (
         SELECT u.name as unit_name, 
                g.name as grade, 
                count(g.name) as nr_count
         FROM units u
         join tests  t on   t.unit_id = u.id
         join grades g on t.score BETWEEN g.`min` and g.`max`
         group by u.id, g.name,u.name 
       ) as t1
group by t1.unit_name ;

Demo

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28