[Solved] Combine multiple rows into one in query results

[Doranwen]

The database I'm basing this off is the one built here: viewtopic.php?f=39&t=98155&p=471031
A short description of some of the table relationships can be found here (along with some queries I was working on): viewtopic.php?f=61&t=98825

A quick summary:
I've got a database that stories info on fanfics (aka fics) I have saved. There's a many-to-many relationship between fanfics and several other tables: authors, fandoms, ships, themes, and characters. This, of course, requires junction tables between each (the database has a total of 15 tables, 7 of which are junction tables (because there's also a many-to-many relationship between fandoms and ships, and fandoms and authors). When I create queries, I generally present all results with author, fanfic title, and a few other items from the Fanfics table (wordcount, rating, weblink), and sometimes fandom, depending on what the query is focusing on (some filter by fandom, but others are filtered by theme, or by one of multiple checkboxes in the Fanfics table).

One issue that I'm starting to be aware of (which will be more of an issue as I enter more data that fits into that category) is the situation of multi-author fics, and multi-fandom fics. For instance, should I run my Favorites query, which pulls all fanfics with a check in the Fav checkbox on the Fanfics table, a fic that appears in two fandoms (and is marked as a favorite) will appear twice in the query results, once under each of the fandom names. The same goes for any fic that was written by more than one author and which fits the other criteria for the query - it would appear twice, once under each author name.

Currently, duplicate results are duplicate in everything except for the double criteria column. In other words, a fic in both fandoms will not be visible as a crossover (multi-fandom) fic in the results except if one notices it's listed twice in the results. A fic written by two authors will not appear as if it's multi-authored unless one happens to notice it appearing under both authors. This is most definitely not the result I would want.

There are two possible results I can imagine:

1) The two separate entries are combined into one single row per fic, so that the Fandom column has "Fandom1, Fandom2" listed in the results. This means that it's easy to see that the fic is a multi-fandom fic at a glance. The downside is that as it appears only once, it would be alphabetized next to Fandom1. If I were to look for fics that are in Fandom2, I would not see that fic because it's not alphabetized next to Fandom2.

2) The unique information is duplicated across both entries. For instance, in the multifandom example above, the fic would appear in results twice - and both times it would have both fandoms listed. One entry would have "Fandom1, Fandom2", and the other would be "Fandom2, Fandom1".

I'm pretty sure the database should have a method of producing result #1. I'm quite less sure of the possibility of #2, but I would really love it. Does anyone know if it's possible? And if not, how do I achieve result #1, at least?

I did some searching regarding combining how I might combine fandoms or authors together, and this link indicates that there is some sort of aggregate function thing that will combine multiple rows into one, but I'm uncertain of how to apply that to my situation: https://www.thepolyglotdeveloper.com/20 ... ingle-row/
My database seems considerably more complicated than all of the example ones - which means almost any query example they have doesn't help much, lol.

Your input and suggestions are much appreciated!

[Villeroy]

https://www.1keydata.com/sql/sqlgroupby.html

Show aggregates (sum,count,min,max,etc) for each exisiting combination of grouped column values.

[Doranwen]

Unfortunately, that doesn't answer any of my questions. Which of the two possibilities I listed is possible (only 1, or both)? How do I apply that to a query joining at least three (usually five or six or seven) tables, with other filtering built in as well? (You can see the complexity of the queries I'm working with in the second link at the top.) The aggregate function page I linked to had more explanation than your link (which seems to involve calculations on numbers in the data, and that's one thing I haven't even tried to do - besides key values, only one of my columns is for numeric data, the rest is text or yes/no), and even then I don't fully understand how to implement it with my database. I appreciate the attempt to help, but I'm still as confused as I was before.

[chrisb]

hello Doranwen,
i believe that both result 1 & result 2 are achievable.
you will need a solution to result 1 before tackling result 2.
hsql 2x has the function 'GROUP_CONCAT' which could be used to implement ("Fandom1, Fandom2, Fandom3") etc. >>> viewtopic.php?p=359135#p359135
you will likely need to use it more than once e.g. "Authors" & "Fandoms".

[Doranwen]

Well, I tried following the example in that link, got nowhere, tried searching for help with GROUP_CONCAT, got this, which appeared marginally easier to follow: http://www.mysqltutorial.org/mysql-group_concat/
But no matter what I try, I get errors in the SQL. Either it's complaining about an unexpected token ( in line 3, or unexpected token FROM in line 8, or something. (At one point in my testing it complained about the ORDER BY at the end.) I don't think I understand this function well enough despite the descriptions of its use, or else my query is more complicated than normal, or both. Because it's not so much a matter of "what am I doing wrong" as "what am I doing in the first place". I don't know why I'm putting what I'm putting where or anything. I do understand basic SQL queries now, having set up more than one. And I sort of understand the complicated ones with the left joins that I was provided in the other thread. But GROUP_CONCAT is unclear as of yet.

This is the one I was trying to modify to group the multifandom and multiauthor fics (below is the original query without any grouping):

Code: Select all

SELECT
	"Fandoms"."Fandom",
	"Authors"."Author",
	"Fanfics"."Title",
	"Fanfics"."Wordcount",
	"Fanfics"."Favorite",
	"Fanfics"."WebLink"
FROM
	"Fanfic_Author",
	"Fanfics",
	"Authors",
	"Fanfic_Fandom",
	"Fandoms"
WHERE "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
AND "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
AND "Fanfics"."Favorite" = 1
ORDER BY "Fandoms"."Fandom" ASC, UPPER ( "Authors"."Author" ) ASC, UPPER ( "Fanfics"."Title" ) ASC

It's designed to return my favorites list to me, which I know has at least two fics on it with multiple fandoms. (The only multi-author fic I've added to the database so far is not a favorite so I'll need to enter one in order to properly test the author concatenation.)

[chrisb]

'GROUP_CONCAT' is an aggregate function.
aggregate functions operate on entire columns & produce a single result.
all non-aggregated fields in the select clause must be included in a 'GROUP BY' clause.
if we have 3 fields A,B,C containing 3 rows of data like so:-
A B C
1,X,Y
1,X,Z
1,X,Y
then we have 2 groups (matching rows of data).
group 1: 2 rows of '1,X,Y'.
group 2: 1 row of '1,X,Z'.

add a column 'D' with values 'Apple', 'Pear', 'Grape'.
so now we have:-
A B C D
1,X,Y, Apple
1,X,Z, Pear
1,X,Y, Grape

select A,B,C, group_concat(D order by D asc separator ', ') as "GroupConcat"
from.......
group by A,B,C
order by C

and the result is:-
A B C GroupConcat
1 X Y Apple, Grape
1 X Z Pear
----------------------------------
i can not see your query result.
you need to determine which fields to aggregate & which fields to group.
at a guess you need to:-
aggregate: "Authors"."Author", "Fanfics"."Title", "Fanfics"."Wordcount" the fields which contain variable multiple values per group.
group: "Fandoms"."Fandom", Fanfics"."Favorite", "Fanfics"."WebLink" the fields which contain identical values per group.

start with this & adjust as necessary.

Code: Select all

SELECT
   "Fandoms"."Fandom",
   group_concat(distinct upper("Authors"."Author") order by upper("Authors"."Author") separator ', ') "Author",
   group_concat(distinct upper("Fanfics"."Title") order by upper("Authors"."Author") separator ', ') "Title",
   group_concat(distinct "Fanfics"."Wordcount" order by upper("Authors"."Author") separator ', ') "Wordcount",
   "Fanfics"."Favorite"
--   "Fanfics"."WebLink"
FROM
   "Fanfic_Author",
   "Fanfics",
   "Authors",
   "Fanfic_Fandom",
   "Fandoms"
WHERE "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
AND "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
AND "Fanfics"."Favorite" = 1
group by "Fandom", "Favorite"--, "WebLink"
ORDER BY "Fandoms"."Fandom" ASC--, UPPER ( "Authors"."Author" ) ASC, UPPER ( "Fanfics"."Title" ) ASC

i do not understand why we have to use upper(). e.g. an author should be unique & identified by the value of id.

[Villeroy]

https://www.1keydata.com/sql/sqlgroupby.html

Show aggregates (sum,count,min,max,etc) for each exisiting combination of grouped column values.

Unfortunately, that doesn't answer any of my questions. Which of the two possibilities I listed is possible (only 1, or both)?

GROUP_CONCAT is an aggregation function just like sum,count,min,max concatenating the text values of a field for each existing combination of grouped fields.

Easy way to convert an embedded HSQLDB to external HSQL2: [Python] Macro to extract and reconnect embedded HSQLDB

[Doranwen]

you need to determine which fields to aggregate & which fields to group.
at a guess you need to:-
aggregate: "Authors"."Author", "Fanfics"."Title", "Fanfics"."Wordcount" the fields which contain variable multiple values per group.
group: "Fandoms"."Fandom", Fanfics"."Favorite", "Fanfics"."WebLink" the fields which contain identical values per group.

Well, what I'm looking at is a single fanfic that appears in multiple fandoms (multi-authored fics being the same idea, just with authors instead of fandoms). From my setup (with fanfics and fandoms connected via a junction table), everything about the fanfic is identical except for the fandom. Which is why I end up with the fanfic twice in my query results. That single fanfic isn't going to have two different titles or wordcounts, and it's unlikely that I'll have multi-authored fics that are also multifandom (I can't think of a single one in my collection, though I won't rule out the possibility). However, there are also fanfics entered with two different authors assigned.

See, this data only appears once in the fanfics table - the title, wordcount, favorites, weblink. So those are all identical. The trouble is with the links to the other tables via junction tables (Fandoms, Authors, Characters, Ships, and Themes - but I'm unlikely to create query results that list the latter three in them - filter by them, yes, but not list them in the results - it's just the Fandoms and Authors that appear in the results and have the potential of duplicating data). FanficID 147, for instance, is entered in the Fanfic_Fandom junction table twice - once with FandomID 11 and once with FandomID 12, because both fandoms are assigned to it. (I only had to enter its data once, but my form's set up so I can assign multiple fandoms to a single fic with that data.) So when I create a query that pulls the fandom along with the fanfic, it grabs a separate copy of the fanfic's data for each of those entries, and that data then appears twice in my query, once under each of the pertinent fandoms. What I'm trying to do is combine the fandom names with a comma and space between e.g. "Fandom11, Fandom12" in my query results, so that it's obvious from one glance at the query results (or the associated report later) that the fic i'm looking at has multiple fandoms (and which fandoms they are).

If a "group" is a fanfic (which is uniquely identified only by the FanficID), then yeah, all of the columns on the Fanfics table are identical to any duplicate records for that FanficID, the Authors and Fandoms might be or might not be. Most fanfics, for instance, only appear in Fanfic_Fandom once, because they only have one fandom assigned. It's the tiny minority that have two assigned (and therefore duplicate rows in my query results) that I want to collapse into one.

Is this making more sense? I'm getting closer to understanding what's going on, but I don't think I'm quite there yet. Would I be right in saying that a "group" in this situation is a single fanfic? And I'm still a little confused about what the aggregate vs. group is all about, or I think I'd have a clear idea of what I'm trying to do here.

i do not understand why we have to use upper(). e.g. an author should be unique & identified by the value of id.

They are - the problem is I nearly always want to sort query results by the author names, and some are lowercase names because these are online nicknames (and not everyone capitalizes those). I keep them in the form that they come to me, some capitalized, some not - and if I don't specify UPPER, I get back all the capitalized ones in alphabetical order, then the lowercase ones in alphabetical order, which is not at all how I want it - I want all alphabetical, regardless of case. So when I was building the database, the kind volunteer helping me suggested that (and that I had to run the SQL directly to make it work - otherwise it would work on the queries but not with reports), and it's worked beautifully ever since. I have to do the same thing with titles. My sort order is generally always fandom, author, title, and while the fandoms all have capital names, the authors and titles most definitely do not, and I want the queries to ignore case when alphabetizing them.

I tried this code, assuming that a group was a single fanfic and that I should use GROUP_CONCAT on the ones that could have multiple values:

Code: Select all

SELECT
	GROUP_CONCAT(DISTINCT "Fandoms"."Fandom" ORDER BY "Fandom" SEPARATOR ', ' ) "Fandom" ,
	GROUP_CONCAT(DISTINCT UPPER ("Authors"."Author") ORDER BY UPPER("Authors"."Author") SEPARATOR ', ' ) "Author",
	"Fanfics"."Title",
	"Fanfics"."Wordcount",
	"Fanfics"."Favorite",
	"Fanfics"."WebLink",
FROM
	"Fanfic_Author", 
	"Fanfics", 
	"Authors", 
	"Fanfic_Fandom" , 
	"Fandoms"
WHERE "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
AND "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
AND "Fanfics"."Favorite" = 1
GROUP BY "Fanfics"."Title", "Fanfics"."Wordcount", "Fanfics"."Favorite", "Fanfics"."WebLink"
ORDER BY "Fandoms"."Fandom" ASC, UPPER ( "Author" ) ASC, UPPER ( "Fanfics"."Title" ) ASC

It errors with "unexpected token: FROM : line: 9" but I can't figure out why it's having issues with Fanfic_Author. So I'm not sure what to do next.

Easy way to convert an embedded HSQLDB to external HSQL2

My database is already a hybrid one, not embedded… The kind volunteer who helped me set it up guided me to doing that as the last thing before I started entering lots of data. It's gotten me learning more SQL (because I can't graphically change the table structure, I have to use SQL commands to do that with the hybrid one).

[chrisb]

Doranwen you have edited the code!!

copy & paste my code into the query design window.
hit the sql icon.
hit execute.

what do you see?

[Doranwen]

Doranwen you have edited the code!!

My apologies - I only did that because the "identical" vs. "varying" values didn't match my data, and I was trying to make it match.

copy & paste my code into the query design window.
hit the sql icon.
hit execute.

what do you see?

"Syntax error in SQL statement"

If I click More, one of the error messages says: "syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE"

[chrisb]

ok i needed to know that the code was valid so i have:-
1) split your db & pasted the code in the query window.
2) menu:Edit> Run SQL command directly.
3) hit F5 (Run Query).
the query gives me the result i was expecting (no errors).
i think that you failed to activate the 'SQL' icon on the top tool bar.

now that i know the code is valid i will pass comment on your code amendments:-
the comma which follows "Fanfics"."WebLink" in the select clause should be deleted.
"Fandoms"."Fandom" is in the 'order by' clause but not in the 'group by' clause.

remember we have used your original query throughout i have simply inserted the function 'GROUP_CONCAT' & by necessity the 'GROUP BY' clause where required.
i have edited your most recent code & it now produces the expected result set. i hope the output is as desired.
if you wish to edit the code then take things one step at a time & test before moving on. you always have the code below to fall back on if/when things become untenable.
now that i understand your use of 'UPPER' i have removed the first instance of it from the 'GROUP_CONCAT' function.

Code: Select all

SELECT
   GROUP_CONCAT(DISTINCT "Fandoms"."Fandom" ORDER BY "Fandom" SEPARATOR ', ' ) "Fandom" ,
   GROUP_CONCAT(DISTINCT ("Authors"."Author") ORDER BY UPPER("Authors"."Author") SEPARATOR ', ' ) "Author",
   "Fanfics"."Title",
   "Fanfics"."Wordcount",
   "Fanfics"."Favorite",
   "Fanfics"."WebLink"
FROM
   "Fanfic_Author",
   "Fanfics",
   "Authors",
   "Fanfic_Fandom" ,
   "Fandoms"
WHERE "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
AND "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
AND "Fanfics"."Favorite" = 1
GROUP BY "Fanfics"."Title", "Fanfics"."Wordcount", "Fanfics"."Favorite", "Fanfics"."WebLink"
ORDER BY "Fandom" ASC, UPPER ( "Author" ) ASC, UPPER ( "Fanfics"."Title" ) ASC

[Doranwen]

*headdesk* I'd opened up a brand-new SQL window to try out your code (previously I was copying existing queries that already had it turned on so I didn't have to click on that) and forgot to turn on the run SQL directly bit. But it's all good - I tried your original code again with the "run SQL directly" turned on and got this:
java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.String java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.String

However, your new bit of code *does* work fine. It produces the first outcome that I had considered - combining the fandoms into one entry only. Thank you! I think I understand what the function is doing now.

You said above that you thought that the second result (the fanfic appearing twice but each time with the other fandom added to the end - one entry being "Fandom1, Fandom2" and the other "Fandom2, Fandom1" - so it could be found under both fandoms but also easily identified as a multifandom fic) was possible. How might that work, or does it look like that isn't possible after all?

[chrisb]

i get no errors whatsoever using hsqldb 2.5.0.jar which version of hsqldb.jar are you using?
if you are not sure then paste this is in the query window & execute:

Code: Select all

select DATABASE_VERSION() version from INFORMATION_SCHEMA.TABLES where TABLE_NAME='TABLES'

i suspect that the guilty statement is

Code: Select all

group_concat(distinct "Fanfics"."Wordcount" order by upper("Authors"."Author") separator ', ') "Wordcount",

-------------------------------------------------------------------------------------
ok so i take it that result 1 has been achieved.
we can go for result 2 if & only if the number of Fandoms is <=2.
my thoughts are:-
1) duplicate our existing code & reverse the order by clause in the duplicated 'GROUP_CONCAT' "Fandom" function.
...NOTE: we can not reverse the order by clause in the duplicated 'GROUP_CONCAT' "Authors" function because additional rows will be created when we have only one "Fandom" but more than one "Author".
2) use the 'UNION' operator to merge the code.
try it out & see what you think:-

Code: Select all

select "Fandom", "Author", "Title", "Wordcount", "Favorite", "WebLink"
from
(
SELECT
   GROUP_CONCAT(DISTINCT "Fandoms"."Fandom" ORDER BY "Fandom" SEPARATOR ', ' ) "Fandom",
   GROUP_CONCAT(DISTINCT ("Authors"."Author") ORDER BY UPPER("Authors"."Author") SEPARATOR ', ' ) "Author",
   "Fanfics"."Title",
   "Fanfics"."Wordcount",
   "Fanfics"."Favorite",
   "Fanfics"."WebLink"
FROM
   "Fanfic_Author",
   "Fanfics",
   "Authors",
   "Fanfic_Fandom",
   "Fandoms"
WHERE "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
AND "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
AND "Fanfics"."Favorite" = 1
GROUP BY "Fanfics"."Title", "Fanfics"."Wordcount", "Fanfics"."Favorite", "Fanfics"."WebLink"

UNION
SELECT
   GROUP_CONCAT(DISTINCT "Fandoms"."Fandom" ORDER BY "Fandom" desc SEPARATOR ', ' ),
   GROUP_CONCAT(DISTINCT ("Authors"."Author") ORDER BY UPPER("Authors"."Author") SEPARATOR ', ' ),
   "Fanfics"."Title",
   "Fanfics"."Wordcount",
   "Fanfics"."Favorite",
   "Fanfics"."WebLink"
FROM
   "Fanfic_Author",
   "Fanfics",
   "Authors",
   "Fanfic_Fandom",
   "Fandoms"
WHERE "Fanfic_Author"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Author"."AuthorID" = "Authors"."AuthorID"
AND "Fanfic_Fandom"."FanficID" = "Fanfics"."FanficID"
AND "Fanfic_Fandom"."FandomID" = "Fandoms"."FandomID"
AND "Fanfics"."Favorite" = 1
GROUP BY "Fanfics"."Title", "Fanfics"."Wordcount", "Fanfics"."Favorite", "Fanfics"."WebLink"
)
ORDER BY "Fandom" ASC, UPPER("Author") ASC, UPPER("Title") ASC

[Doranwen]

It looks like mine's 2.3.2, that might explain why it didn't like that code.

While it's possible to cross three fandoms in the same fic, I'm quite sure I don't have any fics that do, and I'm not likely to ever save one (the sorts that do are special cases). I think I'm safe achieving that result, then - and the code does indeed do that! It's really awesome. I love it. Thank you! (I sort of understand why it works, will keep looking at the query till it makes sense - I've found that if I have a working query on my own database, I'm usually able to follow back to what each bit of the code is doing eventually, and modify it if necessary.)