Grouping the result of a subquery...

[Hyko]

Hello,

is it possible to include the result of a subquery in a GROUP BY clause?

I tried the following code but it always leads to a syntax error...

SELECT sfc.field1, sfc.field2, (SELECT SUM(svr.field8) FROM svr WHERE
(svr.field9<=sfc.field3 AND ...)) as thesubq, SUM(sfc.field4)
FROM sfc
WHERE ...
GROUP BY sfc.field1, sfc.field2, (SELECT SUM(svr.field8) WHERE
(svr.field9<=sfc.field3 AND ...))


And the following does not work as well:
GROUP BY sfc.field1, sfc.field2, thesubq

If I do not try to group the subquery at all access starts to claim that
svr.field9 is not included in the aggregation function... If include these
fields it leads to wrong results...

Do you have any idea except splitting the query? The original query is much
more complex and it would be great to be able to handle it with one
SQL-command from ASP.

Thanks in advance for any hints.

Best,
Hyko

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably better to link the 2 tables together:

SELECT sfc.field1, sfc.field2, SUM(svr.field8) thesubq, SUM(sfc.field4)
FROM sfc INNER JOIN svr
ON sfc.field3 >= svr.field9 AND ...
WHERE ...
GROUP BY sfc.field1, sfc.field2

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+FE9YechKqOuFEgEQJVBQCdFOSj0GZAecPhFPX9nI1gHduqugMAoKpE
MwPD/fSvY3zLjrESn5H9OamX
=hoFV
-----END PGP SIGNATURE-----

 

[giorgio rancati]

Hi Hyko,

try this
----
SELECT field1, field2,
thesubq, Sum(field4)
FROM (SELECT sfc.field1, sfc.field2,
(SELECT SUM(svr.field8)
FROM svr
WHERE (svr.field9<=sfc.field3 AND ...)
) as thesubq,
sfc.field4
FROM sfc
WHERE ...
) AS DRV_TBL
GROUP BY field1, field2, thesubq

 

[Hyko]

@Giorgio, thanks! That was the solution I need. It works great now.

@MGFoster, thanks for your solution, too. It seems that I was mentally fixed
too much on the solution I thought of and did not try other ways to solve
the problem.

Bye, Hyko