Select with Group By not working properly

Question

I am developing an application that needs an embedded database, so I designed it using MYSQL, and created a SQL Statement that works in there. However, when moving to the Embedded Database (Which is Hyper-SQL (HSQLDB)) my query is not working anymore. It seems to be related to the quantity of parameters that I have on the select that must match the Group By, but if I go and add them it'll keep the data ungrouped and that doesn't work for me.

Here is the Query:

I have two tables (GFS and SUPP) with the same columns, the difference is on the behavior and that one has more populated columns that the other one.

SELECT gfs.GFSAccount, gfs.GLAccount,
       CASE 
         WHEN gfs.ICPartner = "" THEN '[ICP_NONE]'
         WHEN gfs.ICPartner <> "" THEN gfs.ICPartner
       END AS ICPartner,
       CASE 
         WHEN gfs.PPESupplementalData <> '' THEN gfs.PPESupplementalData
         WHEN gfs.PPESupplementalData = '' THEN '[NONE]'
       END AS SupplementalData,
       CASE 
         WHEN gfs.AccountType = 'SALES AND COS' THEN gfs.PCode
         WHEN gfs.AccountType = 'COO' THEN 'COO'
         WHEN gfs.AccountType = 'OTHERS' THEN gfs.Business
       END AS Business,
       Round(Sum(gfs.Amount), 2) AS Amount
FROM gfs, supp
WHERE gfs.GLAccount <> supp.GLAccount
GROUP BY gfs.GFSAccount,
         CASE WHEN gfs.ICPartnerSplit = 1 THEN gfs.ICPartner END

UNION

SELECT GFSAccount, GLAccount,
       CASE 
         WHEN ICPartner = "" THEN '[ICP_NONE]'
         WHEN ICPartner <> "" THEN ICPartner
       END AS ICPartner,
       CASE 
         WHEN SuppData <> '' THEN SuppData
         WHEN SuppData = '' THEN '[NONE]'
       END AS SupplementalData,
       Business, Round(Sum(Amount), 2) AS Amount
FROM supp

ORDER BY GFSAccount;

There're a lot of conversions for blank and fixed fields, but the main issue is with the 3 group by (2 within every select, and one in the very end), that it's not allowing me to do it.

(I know that the query itself isn't the best created or optimized, but I've been out from databases for a while and I'm kind of rusty)

Is there any suggestions you guys can do to me in order to make it work in HSQLDB (Or another database that is not MySQL)??

"not working any more" is not the best information you could provide. And it is always helpful to explain what the query is supposed to do *exactly*. — Erwin Brandstetter, Oct 02 '15 at 02:26
Seems like another case of MySQL's rogue implementation of `GROUP BY` biting you. In standard SQL all columns (that are not functionally dependent) and are not listed in the `GROUP BY` clause must be part of an aggregate. MySQL just picks an arbitrary row from each group. Postgres adheres to the standard (mostly), grouping by the primary key covers the rest of the table, other functional dependencies are not observed. Related answers [here](http://stackoverflow.com/a/8440512/939860) or [here](http://stackoverflow.com/a/18993394/939860). — Erwin Brandstetter, Oct 02 '15 at 02:43
I think I'd also be concerned that your join is giving you a (nearly) Cartesian product. What's the point of summing unrelated accounts? — Clockwork-Muse, Oct 02 '15 at 03:30
Unrelated, but: `gfs.ICPartner = ""` is also invalid (standard) SQL. String literals need single quotes, not double quotes. Double quotes are used for quoting identifiers in SQL.So that condition is comparing the column `ICPartner` to a column with a blank name (which most certainly does not exist) — , Oct 02 '15 at 05:56

score 1 · Accepted Answer · answered Oct 02 '15 at 06:54

It appears that you want to select data from the gfs table but only if the GLAccount is not present in the supp table. You need a LEFT JOIN to do that. Otherwise, you need to GROUP BY all columns not used in the sum() function. And you can simplify your CASE statements. For PostgreSQL the query then looks like this:

SELECT gfs.GFSAccount, gfs.GLAccount,
       CASE gfs.ICPartner
         WHEN '' THEN '[ICP_NONE]' ELSE gfs.ICPartner
       END AS ICPartner,
       CASE gfs.PPESupplementalData
         WHEN '' THEN '[NONE]' ELSE gfs.PPESupplementalData
       END AS SupplementalData,
       CASE gfs.AccountType
         WHEN 'SALES AND COS' THEN gfs.PCode
         WHEN 'COO' THEN 'COO'
         WHEN 'OTHERS' THEN gfs.Business
       END AS Business,
       round(sum(gfs.Amount), 2) AS Amount
FROM gfs
LEFT JOIN supp USING (GLAccount)
WHERE supp.GLAccount IS NULL
GROUP BY 1, 2, 3, 4, 5, 6

UNION

SELECT GFSAccount, GLAccount,
       CASE ICPartner
         WHEN '' THEN '[ICP_NONE]' ELSE ICPartner
       END AS ICPartner,
       CASE SuppData
         WHEN '' THEN '[NONE]' ELSE SuppData
       END AS SupplementalData,
       Business, round(sum(Amount), 2) AS Amount
FROM supp
GROUP BY 1, 2, 3, 4, 5, 6

ORDER BY GFSAccount;

If the various columns are NULL instead of an empty string '' you should change the CASE statements to coalesce(gfs.ICPartner, '[ICP_NONE]') AS ICPartner etc.

Just a question, what do the numbers in the GROUP BY refer to? — jarlh, Oct 02 '15 at 06:56

Select with Group By not working properly

1 Answers1