GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END
ORDER BY id ASC SEPARATOR ' ') AS list_with_info,
GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END
ORDER BY id ASC SEPARATOR ' ') AS list_without_info
GROUP BY type ;
Tested at SQL-Fiddle: test-1
Terminology and Methodology
This kind of transformation – rows to columns – is called pivoting. It is typical to pivot data simultaneously with their aggregation, as seems to be a requirement in your case too. In SQL you can do both operations as a single logical step. Other SQL products even offer special syntactical extensions for pivoting, but there is a ...
Unfortunately SQL Server doesn't have an single function to perform group concatenation but there are a few different ways that you can get the result.
You can implement FOR XML PATH and STUFF():
SELECT DISTINCT d.AppId,
Tags = STUFF((SELECT ', ' + t.TagName
FROM AppTags t
where d.AppID = t.AppID
As you don't care about the order of the concatenated items it would be quite easy to knock up a custom CLR aggregate to do this and it will likely out perform the XML method, there is an example of one in this article.
There is a quick and easy change you can make to your existing code though.
SELECT DISTINCT email,
That STUFF FOR XML PATH string concatenation technique sure is cute, but it does not scale very well and across millions of rows it is probably not a very good idea. For larger tables, you may have to write some good old-fashioned procedural SQL with a loop, something like this:
-- Create the working table ...
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP ...
It is possible to get to the desired result in a simpler way.
First, you could start with this simple query:
GROUP_CONCAT(t3.infname ORDER BY t3.infname SEPARATOR ', '),
) AS inftypeandnames
table1 AS t1
INNER JOIN table2 AS t2 ON t1.id = t2.id
You join Completion (columns userID,recordID) to Record (columns userID,ID)
You join Record (column codeID) to Code (column ID)
Here is the proposed query
GROUP_CONCAT(d.description SEPARATOR ',') AS records,
GROUP_CONCAT(c.text SEPARATOR ',') AS text
FROM Completion AS c
INNER JOIN Record AS r ON c.userID=r.userID AND c....
You have identified the source of the problem: that recipe is joined to two tables, recipe_detail and recipe_tagmap (and these to several other tables related to respectively "ingredients" and "tags"), and recipe is having one-to-many relationships with both of them.
One solution is to individually GROUP BY and aggregate first (one aggregation for the list ...
First group by both activity_type and prod_id and then another group by activity_type:
a.activity_type AS Activity,
COUNT(DISTINCT p.id) AS Products,
CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ', ') USING utf8)
product AS p
( SELECT activity_type
The wiscorp.com page SQL Standards has several older revisions and drafts of SQL:20nn (zip):
The 7IWD2-02-Foundation-2011-12.pdf, with a date of 2011-12-21 has at page 289:
If at least one of S1 and S2 is the null value, then the result of the <concatenation> is the null value.
You need to pick one of the string aggregation techniques that works with your version and supports ordering. For example, I've adapted the "ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i" technique from the excellent ORACLE-BASE website:
WITH a as ( SELECT t3_field1,
You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation:
GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, rtg.id)
ORDER BY rtg.id
SEPARATOR ',' -- the comma ',' is the default
How to Order GROUP_CONCAT() Values in MySQL
GROUP_CONCAT(url SEPARATOR '$$' ORDER BY priority)
WHERE project_id = projects.id
) AS images
WHERE catID = 2
You need to use GROUP_CONCAT to aggregate the citizen names first. Then use CONCAT
SELECT CONCAT(id,' # ',name,' # ',citizens) listing FROM
SELECT A.id,A.name,GROUP_CONCAT(B.name,' ',B.surname) citizens
FROM city A INNER JOIN citizen B
ON A.id = B.city_id GROUP BY A.id,A.name
Here is the SQL Fiddle to prove it : http://sqlfiddle.com/#!2/...
The first thing you need is a messy query to create each column
FIRST PHASE OF QUERY
SELECT env,GROUP_CONCAT(CONCAT(inftype,' [',names,']')
ORDER BY inftype DESC SEPARATOR ' ') tags
FROM (SELECT env,inftype,GROUP_CONCAT(infname ORDER BY infname SEPARATOR ', ') names
FROM (SELECT AAA.id,BBB.infid,BBB.env,CCC.inftype,CCC.infname
FROM table1 AAA
INNER JOIN ...
You've already found your answer, but here are the reasons:
The reason you're getting duplicates is because you're joining tab1 with itself without criteria on the join, which is simply resulting in duplicates. The query can be pretty easily accomplished without the join, looking like this:
SELECT A.team, A.name, GROUP_CONCAT(A.count SEPARATOR ' , ')
You need to apply one small change to your query which is emphasised below:
CONCAT('[', GROUP_CONCAT(event_dates.id SEPARATOR ','), ']') AS event_date_ids,
CONCAT('[', COALESCE(GROUP_CONCAT(event_times_per_event_date.event_time_ids SEPARATOR ','), ''), ']') AS event_time_ids
INNER JOIN event_dates ON event_dates.event_id = ...
In this case I'd suggest to use a subquery to count rows using a variable.
select user_id, name, group_concat(login_date)
from (select user_id, login_date, name
from (select user_id, login_date, name,
if(@last_user = user_id, @rn := @rn + 1, @rn := 0) rn,
@last_user := user_id
To create the result you wanted, I used a subquery and used GROUP_CONCAT() from there.
create table test
insert into test
SELECT COUNT(event) AS total FROM test GROUP BY event
You could group by event in this way:
group_concat(id, ',') ids,
group by event;
select event, group_concat(id, ',') ids, count(*) cnt
group by event
event | ids | cnt
:---- | :---------- | --:
a | 22, | 1
bb | 23,,24, | 2
ccc | 25,,26,,27, | 3
As per MySQL BOL Here
The maximum value for group_concat_max_len for 64-bit is
The maximum value for group_concat_max_len for 32-bit is
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher,...
The error is likely cause because the "cycle" isn't closed, i.e. last point in the polygon does not match the first one.
This can be from two issues:
the group_concat_max_len setting is too low (default is 1024).
Solution: increase it, either in the server or session level.
the error-prone GROUP BY used to add the additional point.
The GROUP BY you ...
You seem to have 2 questions.
Is there something better than DISTINCT in GROUP_CONCAT()? No. Use it when you need it.
Can the query be sped up or otherwise improved?
Don't use IN ( SELECT ... ), use EXISTS or JOIN.
JOIN + GROUP BY smells like the inefficient "explode-implode" pattern. First you build a large set or rows (from the joins), then you ...
Your derived table is missing the extra field it is looking for try this:
IF(fence.fenceName IS NULL, alarmType.alarmName,
To alias the expression computed inside a Correlated Subquery, you will need to specify it outside the complete subquery. In this case, it will be outside the If(..) block:
Group_concat(p.product_name, ' x', p.product_quantity ORDER BY
'<br>') AS items,
This is not an answer to your question, but it was a bit long for a comment so I'll add it here. If not mistaken, since:
inner join Action
on (Action.emp_Id = Employee.id
and Action.emp_Id in (select Employee.id
where Employee.vendor_Id = 1))
You can get it by using group_concat()
create table t (notes varchar(200), id int, name varchar(100), emp_Id int);
insert into t values ('1. Finish Designing Queries', 10, 'John', 2);
insert into t values ('2. Finish Designing Queries', 10, 'Jack', 5);
insert into t values ('3. Other note', 20, 'Jack', 5);
select notes, id, group_concat(name, ':', emp_id)
No index is useful. There is only one value of usergroupID (as you have written the query), so there is no use for it to be indexed.
So, the Optimizer decides to simply read the data without using any index.
One thing you can do is to get rid of the extra layer of SELECTs.
Another thing is to get rid of assignmentCount, since it is not used.
To achieve ...
I appreciate this question is a bit old now, but in case someone finds it and is wondering, one downside of setting the maximum (or an otherwise very large) value is that group_concat can return a blob rather than a varchar. Suggestions elsewhere say to set group_concat_max_len to 512 to make it always return a varchar rather than a blob. I tend to just cast ...