Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – using group_concat gives error while grouping columns

Given table called events with columns dt (timestamp) and type (event type, example buy, sell, other)

I want to get all the events that happened in 2021 in Quarter wise.

The result should hold 2 columns:

  • column1 = Quarter number followed by single quote and then last 2 digits of year.
    example Q1’21, Q2’21, Q3’21, Q4’21

  • column2 = Format is ##=##, first ## indicates the type of event, and next # indicates total number of events of this particular type.

    • Separate the types by semicolon and space.
    • Sort this by descending order by total number of events and then the ascending order of type.

  • Sort the results in ascending order of column1.

I am able to write query to get individual columns, but when I combine them together I am getting errors with group columns.

Here is my query:

  • To get column1:
select concat('Q', Quarter(dt), "'", RIGHT(YEAR(dt), 2)) as c1 
from events
  • To get column2:
select group_concat(concat(type, '=', cnt)) as c2 
from (
    select type, count(*) as cnt, quarter(dt) as qtr
    from events where year(dt) = 2021 
    group by type, qtr

What is the correct way to solve this?

sample input:

2021-02-10 12:10:32    sell
2021-01-10 01:10:32    buy
2021-03-10 13:10:32    other
2021-11-10 13:10:32    buy
2021-12-10 13:10:32    sell

sample output:

Q1'21    buy=1; other=1; sell=1
Q4'21    buy=1; sell=1
Tags:

2

Answers


  1. 0

    Include the year in the subquery’s grouping.

    SELECT CONCAT(q, "'", y) AS c1, GROUP_CONCAT(type, '=', cnt ORDER BY cnt DESC, type) AS c2
FROM (
    SELECT type, QUARTER(dt) AS q, RIGHT(YEAR(dt), 2) AS y, COUNT(*) AS cnt
    FROM events
    GROUP BY type, y, q
)
GROUP BY c1

    Note that it’s not necessary to use CONCAT() inside GROUP_CONCAT(), it automatically concatenates all the arguments.

    Login or Signup to reply.
  2. 0

    Data

    CREATE TABLE YOURTABLE(
   date   DATETIME   NOT NULL 
  ,opinon VARCHAR(80) NOT NULL
);
INSERT INTO YOURTABLE
(date,opinon) VALUES 
('2021-02-10 12:10:32','sell'),
('2021-01-10 01:10:32','buy'),
('2021-03-10 13:10:32','other'),
('2021-11-10 13:10:32','buy'),
('2021-12-10 13:10:32','sell');

    use subquery, concat, Group_concat and right as follows

    SELECT date1,
       Group_concat(opinon ORDER BY date1 SEPARATOR ';') OPINION
FROM   (SELECT Concat('Q', Quarter(date), '''', RIGHT(Year(date), 2)) DATE1,
               Concat(opinon, '=1')                                   opinon
        FROM   yourtable) A
GROUP  BY date1

    dbfiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search