Group_Concat in SQLite

Sqlite group_concat ordering

Can you not use a subselect with the order by clause in, and then group concat the values?

Something like

SELECT ID, GROUP_CONCAT(Val)
FROM (
   SELECT ID, Val
   FROM YourTable
   ORDER BY ID, Val
   )
GROUP BY ID;

How to do group_concat on two fields in sqlite?

In SQLite GROUP_CONCAT() takes 2 arguments: the expression to be aggregated and the separator (the default separator is ,).

In your case, you need multiple columns for the expression part so you must build it by concatenation:

SELECT year, 
       GROUP_CONCAT(id || ',' || city) AS id_city
FROM cities
GROUP BY year;

See the demo.

GROUP_CONCAT in SQLite

You need to add GROUP BY clause when you are using aggregate function. Also use JOIN to join tables.

So try this:

SELECT AI._id, GROUP_CONCAT(Name) AS GroupedName
  FROM ABSTRACTS_ITEM AI 
  JOIN AUTHORS_ABSTRACT AAB ON AI.ID = AAB.ABSTRACTSITEM_ID
  JOIN ABSTRACT_AUTHOR AAU ON AAU._id = AAB.ABSTRACTAUTHOR_ID
 GROUP BY tbl._id;

See this sample SQLFiddle

---

What you were trying was almost correct. You just needed to add GROUP BY clause at the end. But the first one is better.

SELECT ID,
GROUP_CONCAT(NAME) 
FROM
    (select ABSTRACTS_ITEM._id AS ID,
     Name
     from
    ABSTRACTS_ITEM , ABSTRACT_AUTHOR , AUTHORS_ABSTRACT
    where
    ABSTRACTS_ITEM._id = AUTHORS_ABSTRACT.ABSTRACTSITEM_ID
    and
    ABSTRACT_AUTHOR._id = AUTHORS_ABSTRACT.ABSTRACTAUTHOR_ID)
GROUP BY ID;

SQLite Group_Concat

Use CASE expressions and the second parameter of group_concat():

SELECT Day,
       group_concat(CASE Score WHEN 'A' THEN Person END, '+') AS Res_A,
       group_concat(CASE Score WHEN 'B' THEN Person END, '+') AS Res_B,
       group_concat(CASE Score WHEN 'C' THEN Person END, '+') AS Res_C
FROM MyTable
GROUP BY Day;

How to avoid duplication in GROUP_CONCAT?

GROUP_CONCAT(DISTINCT g.value)

Why is GROUP_CONCAT in this SQLite query not working in recent versions?

Actually the correct results that you got with the older version of SQLite that you used were coincidentally correct.

In your code you group by only 1 column and select many columns that are not present in the GROUP BY clause.

This is not allowed in most databases, but SQLite allows it and this leads to unexpected results as SQLite picks an (almost) arbitrary row to select the values for all the unaggregated columns.

As it is your code, yo can use aggregation with the function MAX() for the column Children to get a non-null value (if it exists):

SELECT ID, Title_PT, Title_DE, Parent_ID ,
       MAX(Children) AS Children,
       Sentences
FROM (
    SELECT a.*,
           GROUP_CONCAT(st.ID) AS Sentences
    FROM (
        SELECT sc.ID, sc.Title_PT, sc.Title_DE, sc.Parent_ID, GROUP_CONCAT(sc2.ID) AS Children
        FROM Section AS sc
        LEFT JOIN Section AS sc2 ON sc2.Parent_ID = sc.ID
        GROUP BY sc2.Parent_ID
    ) AS a
    LEFT JOIN Sentence AS st ON st.Section_ID = a.ID
    GROUP BY a.ID
    UNION
    SELECT sc.ID, sc.Title_PT, sc.Title_DE, sc.Parent_ID, NULL AS Children, GROUP_CONCAT(st.ID) AS Sentences
    FROM Section AS sc
    LEFT JOIN Sentence AS st ON st.Section_ID = sc.ID
    GROUP BY sc.ID
)
GROUP BY ID
ORDER BY Parent_ID;

See the demo.

group_concat and how to use row number in sqlite

There is no need for GROUP_CONCAT() functionality in your case. Your last query in the question is for SQL Server. Sqlite doesn't have implementation for ROW_NUMBER().

This being said, try

SELECT 
 (
   SELECT COUNT(*) 
     FROM 
   ( SELECT 1
       FROM dataPetak
      WHERE id <= t.id
      GROUP BY DATE(datetime)
   ) q
 ) No, datetime, count
  FROM 
 (
   SELECT id, MIN(datetime) datetime, COUNT(*) count
     FROM dataPetak
    GROUP BY DATE(datetime)   
 ) t

Output:

| No |            datetime | count |
------------------------------------
|  1 | 2013-05-24 19:23:16 |     1 |
|  2 | 2013-05-28 19:24:20 |     2 |
|  3 | 2013-05-30 19:25:39 |     2 |

Here is SQLFiddle demo

---

Related Topics