[Solved] GROUP_CONCAT HSQL

[jdwilcock]

I download online orders as a csv file. Then I import them. Each order has an OrderNumber. However, each order can have multiple items. So I get multiple rows with the same OrderNumber that each have a different item what was ordered. I would like to be able to combine the items onto one row. So there would be just one row for each OrderNumber and the Item field would have all of the items separated by commas.

The code below works with mysql but not with hsql.

Code: Select all

SELECT "Key" , "OrderNumber",        
"OrderDate","OrderTime","IP","OrderID", GROUP_CONCAT("Item" ) as "Item", "Describe" ,"OneTime","Color","Size","AddOn1" FROM table1 
GROUP BY OrderNumber;

I did get GROUP_CONCAT to work with hsql but only using 2 fields: OrderNumber and Item. When I use more than 2 fields in the statement it doesn't work. Any ideas? Does Group_Concat not function the same way as mysql?

[jdwilcock]

By the way I'm using HSQLDB 2.3.2 Split with OpenOffice Base.

[Sliderule]

When I use more than 2 fields in the statement it doesn't work. Any ideas?

You are using an Aggregate Function . . . GROUP_CONCAT and therefore, each column in your result set MUST be either:

1. An Aggregate Function
2. Included in the Group By clause

Also, a few of your column / field / table names are NOT surrounded by double quotes, even though they are NOT UPPER CASE.

You wrote:

Code: Select all

SELECT 
   "Key", 
   "OrderNumber", 
   "OrderDate",
   "OrderTime",
   "IP",
   "OrderID", 
   GROUP_CONCAT("Item" ) as "Item", 
   "Describe",
   "OneTime",
   "Color",
   "Size",
   "AddOn1" 
FROM table1 
GROUP BY OrderNumber;

Now, try this, just as an example:

Code: Select all

SELECT 
   -- "Key", 
   "OrderNumber", 
   -- "OrderDate",
   -- "OrderTime",
   -- "IP",
   -- "OrderID", 
   GROUP_CONCAT("Item" ) as "Item", 
   -- "Describe",
   -- "OneTime",
   -- "Color",
   -- "Size",
   -- "AddOn1" 
FROM "table1" 
GROUP BY "OrderNumber";

Now, try this, just as another example:

Code: Select all

SELECT 
   -- "Key", 
   "OrderNumber", 
   -- "OrderDate",
   -- "OrderTime",
   -- "IP",
   -- "OrderID", 
   GROUP_CONCAT(CONCAT_WS(' - ', "Item", "Describe", "Color", "Size")) as "Item", 
   -- "OneTime",
   -- "AddOn1" 
FROM "table1" 
GROUP BY "OrderNumber";

Explanation:

1. The above, for each UNIQUE "Order Number" the database back-end ( HSQL 2.3.? ) will use the Aggregate Function GROUP_CONCAT to return the "Item" separated by a comma.
2. Since, I do NOT really know, what you want the result set to look like, I just commented count ( -- ) some of the columns.
3. The second example, is using the HSQL 2.3 function, CONCAT_WS to concatenate the content of a few columns, each separated by text string ' - ' to comprise the GROUP_CONCAT column "Item" .
4. If you want to include, in the GROUP_CONCAT aggregate function, using the CONCAT_WS function, a Date, Time, or TimeStamp column, you should use the HSQL function TO_CHAR to return the value as a string.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[jdwilcock]

Thank you Sliderule.

I still can't make it work right. I tried including all the fields in the "Group By" except for "ID". I've tried lots of variations. I can get it to work if I only use fields like OrderNumer, IP, OrderID, which are all the same for each order. But If I include the Key field it doesn't work.

Code: Select all

   SELECT
       "Key", "OrderNumber", "IP",  "OrderID",
       GROUP_CONCAT("ID" ) as "ID",
       "Describe",
       "OneTime"      
    FROM "table1"
    GROUP BY "OrderNumber";

This is what I would like the result to look like:


Order# 161992 was for 3 different items "ID's". So instead of having a separate line for each "ID", it is combined into one line with the "ID's" separated by commas. There will be more fields after the "OneTime" field that I would also include in the result set.

[Sliderule]

I am sorry, but, it is not clear to me . . . you said:

Order# 161992 was for 3 different items "ID's". So instead of having a separate line for each "ID", it is combined into one line with the "ID's" separated by commas. There will be more fields after the "OneTime" field that I would also include in the result set.

Therefore, for what UNIQUE value, do you want/desire to use, with the GROUP_CONCAT ?

Maybe, you can include ( Append both ) the content of your database here as a ZIP file, and, your *.odb database file . . . OR . . . if you want to work with me privately, send me a Private Message .

Please try the following, but, again, I am NOT at all sure this is what you want, but, try it please.

1. 
   

   Code: Select all

   SELECT
       GROUP_CONCAT("Key") as "Key", 
       "OrderNumber", 
       "IP",  
       "OrderID",
       GROUP_CONCAT("ID") as "ID",
       MIN("Describe") as "Describe",
       "OneTime"      
   FROM "table1"
   GROUP BY "OrderNumber",
            "IP",
            "OrderID",
            "OneTime"
   

2. The following MUST be executed in Run SQL command directly . . . because of the OpenOffice / LibreOffice Base Parser.
   

   Code: Select all

   SELECT
       GROUP_CONCAT( DISTINCT "Key") as "Key", -- Since have DISTINCT must be Run Direct
       "OrderNumber", 
       "IP",  
       "OrderID",
       GROUP_CONCAT("ID") as "ID",
       MIN("Describe") as "Describe",
       "OneTime"      
   FROM "table1"
   GROUP BY "OrderNumber",
            "IP",
            "OrderID",
            "OneTime"
   

Explanation:

1. In the above, each column used in the SELECT clause, that does NOT include an Aggregate Function ( GROUP_CONCAT, MIN ) is included in the GROUP BY clause. Yes, you can use either a MIN or MAX function on a text string ( VARCHAR ).
2. The second Query, because of the Base Parser ( unlike the abilities of HSQL 2.3 ), has to be Run in Direct Mode . . . because of the use of the keyword DISTINCT in use with GROUP_CONCAT and the column "Key"

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

Sliderule

[jdwilcock]

Wow! You got it to work. Thank you so much.

Now I have a new problem: Inserting the query results into a different table.

Code: Select all

SELECT * INTO "table2" FROM

(SELECT
    GROUP_CONCAT("Key") as "Key",
    "OrderNumber", "IP", "OrderID",
    GROUP_CONCAT("ID") as "ID",
    MIN("Describe") as "Describe",   "OneTime"     
FROM "table1"
GROUP BY "OrderNumber", "IP", "OrderID", "OneTime");

It's probably really simple but I haven't been able to get it right.

[Sliderule]

Wow! You got it to work. Thank you so much.

Now I have a new problem: Inserting the query results into a different table.

¿ Why ?

Are you sure you want to put ( duplicate ) some data into a separate table? If it were me, I would create a View . . . this way, any change you make to the data, the View will now contain the desired data, and, you use it just like a Table . . . using the Query you defined.

From the Menu, Tools -> SQL... :

Code: Select all

CREATE VIEW "table2" AS 
   SELECT
       GROUP_CONCAT("Key") as "Key",
       "OrderNumber", "IP", "OrderID",
       GROUP_CONCAT("ID") as "ID",
       MIN("Describe") as "Describe",   "OneTime"     
   FROM "table1"
   GROUP BY "OrderNumber", "IP", "OrderID", "OneTime"

Of course, if it were me, I would not name it . . . "table2" but, perhaps, "table1_view2" ( or whatever you want ).

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[jdwilcock]

I need to insert the query results into a table so I can run further updates on the table. I won't be duplicating any data. The table is temporary and it will be truncated before I insert the query results into it.

[Sliderule]

I need to insert the query results into a table so I can run further updates on the table. I won't be duplicating any data. The table is temporary and it will be truncated before I insert the query results into it.

OK, now I think I understand ( I wanted to make sure it was not attempting to create a TEXT table ).

Try the following command from the Base Menu: Tools -> SQL...

Code: Select all

-- The following SQL statement ( DML - Data Modification Language ) 
-- will ADD data into "table2" from the following Select Query
-- NOTE: assumption "table2" columns and column type are the same as
--       the result set from the Select Query and all rules of Primary Key in "table2" are met
INSERT INTO "table2" 
   (SELECT
       GROUP_CONCAT("Key") as "Key",
       "OrderNumber", "IP", "OrderID",
       GROUP_CONCAT("ID") as "ID",
       MIN("Describe") as "Describe",   "OneTime"     
    FROM "table1"
    GROUP BY "OrderNumber", "IP", "OrderID", "OneTime");

Explanation:

• Please refer to the HSQL 2.3 documentation for an INSERT INTO statement . . . at:
  
  http://www.hsqldb.org/doc/2.0/guide/dat ... _statement
• Note the use of parentheses around the Select statement.
• Sliderule comments start with two dashes.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[jdwilcock]

Thank you Sliderule. You have been very patient and helpful.