[Solved] GROUP_CONCAT HSQL

Discuss the database features

[Solved] GROUP_CONCAT HSQL

Postby jdwilcock » Thu Jul 30, 2015 7:39 pm

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   Expand viewCollapse view
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?
Last edited by jdwilcock on Wed Aug 05, 2015 7:37 pm, edited 1 time in total.
OpenOffice 4.0.1 on Windows 7
jdwilcock
 
Posts: 16
Joined: Fri Dec 06, 2013 11:56 pm

Re: GROUP_CONCAT HSQL

Postby jdwilcock » Thu Jul 30, 2015 7:45 pm

By the way I'm using HSQLDB 2.3.2 Split with OpenOffice Base.
OpenOffice 4.0.1 on Windows 7
jdwilcock
 
Posts: 16
Joined: Fri Dec 06, 2013 11:56 pm

Re: GROUP_CONCAT HSQL

Postby Sliderule » Fri Jul 31, 2015 12:12 am

jdwilcock wrote: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. :mrgreen:

You wrote:

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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: :mrgreen:

Code: Select all   Expand viewCollapse view
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" . :fist:
  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. 8-)
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.
User avatar
Sliderule
Volunteer
 
Posts: 1241
Joined: Thu Nov 29, 2007 9:46 am

Re: GROUP_CONCAT HSQL

Postby jdwilcock » Fri Jul 31, 2015 5:33 pm

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   Expand viewCollapse view
   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:

Image
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.
OpenOffice 4.0.1 on Windows 7
jdwilcock
 
Posts: 16
Joined: Fri Dec 06, 2013 11:56 pm

Re: GROUP_CONCAT HSQL

Postby Sliderule » Fri Jul 31, 2015 5:49 pm

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   Expand viewCollapse view
    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   Expand viewCollapse view
    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
User avatar
Sliderule
Volunteer
 
Posts: 1241
Joined: Thu Nov 29, 2007 9:46 am

Re: GROUP_CONCAT HSQL

Postby jdwilcock » Tue Aug 04, 2015 4:49 pm

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   Expand viewCollapse view
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.
OpenOffice 4.0.1 on Windows 7
jdwilcock
 
Posts: 16
Joined: Fri Dec 06, 2013 11:56 pm

Re: GROUP_CONCAT HSQL

Postby Sliderule » Tue Aug 04, 2015 5:04 pm

jdwilcock wrote: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 :bravo: . . . using the Query you defined.

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

Code: Select all   Expand viewCollapse view
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 ). :D

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.
User avatar
Sliderule
Volunteer
 
Posts: 1241
Joined: Thu Nov 29, 2007 9:46 am

Re: GROUP_CONCAT HSQL

Postby jdwilcock » Tue Aug 04, 2015 10:24 pm

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.
OpenOffice 4.0.1 on Windows 7
jdwilcock
 
Posts: 16
Joined: Fri Dec 06, 2013 11:56 pm

Re: GROUP_CONCAT HSQL

Postby Sliderule » Wed Aug 05, 2015 2:19 am

jdwilcock wrote: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   Expand viewCollapse view
-- 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:
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.
User avatar
Sliderule
Volunteer
 
Posts: 1241
Joined: Thu Nov 29, 2007 9:46 am

Re: [SOLVED] GROUP_CONCAT HSQL

Postby jdwilcock » Wed Aug 05, 2015 7:37 pm

Thank you Sliderule. You have been very patient and helpful.
OpenOffice 4.0.1 on Windows 7
jdwilcock
 
Posts: 16
Joined: Fri Dec 06, 2013 11:56 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests