CODEWITHSUNDEEP
mysql
zacken542
zacken542

Reputation: 11

Mysql error on group_concat

I posted in this link but I should have posted a new

I am beginner on MySQL I tried exampleon link above but get some error codes

MY select looks like this

SELECT YEAR(TimeStamp) AS "YEAR", MONTHNAME(TimeStamp) AS "MONTH", DAY(TimeStamp) AS "DAY",
       min(Temperature) AS "min", max(Temperature) AS "max",
       ROUND(AVG(Temperature),1) AS "avg",
       substring_index(group_concat(TimeStamp order by Temperature asc), ',', 1) as min_TimeStamp,
       substring_index(group_concat(TimeStamp order by Temperature desc), ',', 1) as max_TimeStamp
FROM ute_temp_min
    WHERE TimeStamp BETWEEN '2014-11-05' and NOW()
GROUP BY YEAR(TimeStamp), MONTH(TimeStamp),  DAY(TimeStamp);

this is my input table

index","TimeStamp","Temperature"
2721909,"2014-11-05 00:00:08",1.4
2721910,"2014-11-05 00:01:12",1.4
2721911,"2014-11-05 00:02:13",1.4
2721912,"2014-11-05 00:03:06",1.4

this is my output and it looks at it's OK

"YEAR","MONTH","DAY","min","max","avg","min_TimeStamp","max_TimeStamp"
2014,"November",5,-3.4,1.4,-0.6,"2014-11-05 22:15:16","2014-11-05 00:28:12"
2014,"November",6,-2.6,0.3,-0.9,"2014-11-06 01:34:08","2014-11-06 12:52:12"

But I get this error (more of them but same but different rows

Row 52 was cut by GROUP_CONCAT() errornr 1260

What is my mistake?

Sven Åke

Upvotes: 1

Views: 2111

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

try setting your session max length. I have been using it all week to fix a data integrity issue on our servers so group concat is very useful for that (to visually see users that shouldn't be linked together) but i too had an issue with data getting cut off

try this

SET SESSION group_concat_max_len = 999999999;
SELECT ... -- your query here

the awesome thing about this is its only in your current session (its a temporary function).. so you aren't allocating memory on the server(disk space) so it wont slow things down any. the length is changed back to its default value (1024) after your session ends!

EDIT:

a little cleanup on the query you have

SELECT 
    YEAR(TimeStamp) AS "YEAR", 
    MONTHNAME(TimeStamp) AS "MONTH", 
    DAY(TimeStamp) AS "DAY",
    min(Temperature) AS "min", 
    max(Temperature) AS "max",
    ROUND(AVG(Temperature),1) AS "avg",
    substring_index(group_concat(TimeStamp order by Temperature asc), ',', 1) as min_TimeStamp,
    substring_index(group_concat(TimeStamp order by Temperature desc), ',', 1) as max_TimeStamp
FROM ute_temp_min
WHERE DATE(TimeStamp) BETWEEN '2014-11-05' and CURDATE()
GROUP BY DATE(TimeStamp);

notice I changed the WHERE to do a date comparison (so MySQL isn't having to convert your string date to a datetime by itself)
I also changed the GROUP BY to be on DATE() to do less calculations

Upvotes: 1

Related Questions