Hi all

Please take a moment to help.

In a sub query I want to use a field from the query as Criteria.
Is it possible?

eg.
SELECT table_1.field_1 as alias_1,
(SELECT SUM(some_field) WHERE some_field= alias_1) AS TEST
etc etc ...

Thank you for your time and the great help you
provide

best regards
Nikos

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Nikos" <pappas@c...>
Subject: [asp_databases] SQL SubQuery


: Please take a moment to help.
:
: In a sub query I want to use a field from the query as Criteria.
: Is it possible?
:
: eg.
: SELECT table_1.field_1 as alias_1,
: (SELECT SUM(some_field) WHERE some_field= alias_1) AS TEST
: etc etc ...
:
: Thank you for your time and the great help you
: provide

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

It really depends on your database schema. If the two tables are related,
probably most efficient way would be:

SELECT
    a.Field1,
    SUM(b.Field2)
FROM
    Table1 AS a
INNER JOIN
    Table2 AS b
ON
    a.Table1ID = b.Table2ID
GROUP BY
    a.Field1

On the other hand, if you really need to do the subquery, then you can do
(for example):

SELECT
    a.Field1,
    (
    SELECT
        SUM(b.Field2)
    FROM
        Table1 AS b
    WHERE
        b.Field1 = a.Field1
    )
FROM
    Table1 AS a
WHERE
    ...

Cheers
Ken

>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>From: "Nikos" <pappas@c...>
>Subject: [asp_databases] SQL SubQuery
>
>
>: Please take a moment to help.
>:
>: In a sub query I want to use a field from the query as Criteria.
>: Is it possible?
>:
>: eg.
>: SELECT table_1.field_1 as alias_1,
>: (SELECT SUM(some_field) WHERE some_field= alias_1) AS TEST
>: etc etc ...
>:
>: Thank you for your time and the great help you
>: provide
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>It really depends on your database schema. If the two tables are related,
>probably most efficient way would be:
>
>SELECT
>     a.Field1,
>     SUM(b.Field2)
>FROM
>     Table1 AS a
>INNER JOIN
>     Table2 AS b
>ON
>     a.Table1ID = b.Table2ID
>GROUP BY
>     a.Field1
>
>On the other hand, if you really need to do the subquery, then you can do
>(for example):
>
>SELECT
>     a.Field1,
>     (
>     SELECT
>         SUM(b.Field2)
>     FROM
>         Table1 AS b
>     WHERE
>         b.Field1 = a.Field1
>     )
>FROM
>     Table1 AS a
>WHERE
>     ...
>
>Cheers
>Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Dear Ken
Thank you for your time and great help.

I am sending some code.
If you find the time please check this out.

The database has 2 tables agents_T and apointments_T
Some of the fields in it:

agents_T
-------------------------------------
agents_T.ag_department
agents_T.ag_unit
agents_T.surname
agents_T.name
agents_T.code      (primary key (text) related one to many to 
apointments_T.code)

apointments_T
------------------------------------
apointments_T.code
apointments_T.apointments     how many apointments with potential customers 
the agent did
apointments_T.policies            how many policies the agent sold
apointments_T.premiums         currency


the sql statement
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL = "SELECT agents_T.ag_department AS 
DEPARTMENT,agents_T.ag_unit,agents_T.surname,agents_T.name,randevou_T.code,"
SQL = SQL & " IIf("
SQL = SQL & " SUM(randevou_T.policies) = 0,0,"
SQL = SQL & " Sum(randevou_T.premiums) / Sum(randevou_T.policies)"
SQL = SQL & " ) AS AV_POLICIES_PREMIUM,"

Continues with more calculations like this without problem

Now I want to compare agents production Sum(randevou_T.premiums)
against the departments production. Is something like the following 
sub_query possible,
so I could have in every agents record his departments sum too ?

(SELECT Sum(randevou_T.premiums) WHERE agents_T.ag_department = DEPARTMENT) 
AS DEPARTMENTS_PRODUCTION

The rest of the statement is fine as well.

SQL = SQL & " FROM agents_T LEFT JOIN randevou_T ON agents_T.code = 
randevou_T.code"
GROUP BY 
agents_T.ag_department,agents_T.ag_unit,agents_T.surname,agents_T.name,randevou_T.code"

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Many thanks again and please be patient with my knowledge and English.
Suggest a good book for SQL statements.

Best regards

Nikos