Can use Subquery like this?

14 Jul 2005 3:26 PM

Bongee

Hello,

SELECT B.CODE, C.CURCODE,
       SUM(A.AMOUNT) AS TL_TOPLAM,
       SUM(A.TRNET) AS DOVIZ_TOPLAM,
       SUM(A.TRNET * (SELECT RATES1 FROM L_DAILYEXCHANGES WHERE LREF = 1) )
AS FARK
FROM LG_001_01_CLFLINE A LEFT JOIN LG_001_CLCARD B ON A.CLIENTREF =
B.LOGICALREF
                         LEFT JOIN L_CURRENCYLIST C ON A.TRCURR = C.CURTYPE
WHERE A.TRCURR != 0
GROUP BY B.CODE, C.CURCODE

(SELECT RATES1 FROM L_DAILYEXCHANGES WHERE LREF = 1) ?????

Thanks

14 Jul 2005 3:29 PM

Aaron Bertrand [SQL Server MVP]

Unless that table only ever has one row, you need to narrow it down to one
row by relating it to the rest of the query.

Please see http://www.aspfaq.com/5006

Show quote

"Bongee" <bong***@bonbon.net> wrote in message
news:uipvshIiFHA.2156@TK2MSFTNGP14.phx.gbl...
> Hello,
>
> SELECT B.CODE, C.CURCODE,
>       SUM(A.AMOUNT) AS TL_TOPLAM,
>       SUM(A.TRNET) AS DOVIZ_TOPLAM,
>       SUM(A.TRNET * (SELECT RATES1 FROM L_DAILYEXCHANGES WHERE LREF = 1) )
> AS FARK
> FROM LG_001_01_CLFLINE A LEFT JOIN LG_001_CLCARD B ON A.CLIENTREF =
> B.LOGICALREF
>                         LEFT JOIN L_CURRENCYLIST C ON A.TRCURR = C.CURTYPE
> WHERE A.TRCURR != 0
> GROUP BY B.CODE, C.CURCODE
>
>
> (SELECT RATES1 FROM L_DAILYEXCHANGES WHERE LREF = 1) ?????
>
> Thanks
>
>

14 Jul 2005 3:33 PM

Bongee

I have one row but the error message is

Server: Msg 130, Level 15, State 1, Line 4
Cannot perform an aggregate function on an expression containing an
aggregate or a subquery.

Problem?

Thanks

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa>, haber iletisinde

þunlarý yazdý:uz3TQjIiFHA.1***@tk2msftngp13.phx.gbl...
Show quote

> Unless that table only ever has one row, you need to narrow it down to one
> row by relating it to the rest of the query.
>
> Please see http://www.aspfaq.com/5006
>
>
>
> "Bongee" <bong***@bonbon.net> wrote in message
> news:uipvshIiFHA.2156@TK2MSFTNGP14.phx.gbl...
>> Hello,
>>
>> SELECT B.CODE, C.CURCODE,
>>       SUM(A.AMOUNT) AS TL_TOPLAM,
>>       SUM(A.TRNET) AS DOVIZ_TOPLAM,
>>       SUM(A.TRNET * (SELECT RATES1 FROM L_DAILYEXCHANGES WHERE LREF =
>> 1) ) AS FARK
>> FROM LG_001_01_CLFLINE A LEFT JOIN LG_001_CLCARD B ON A.CLIENTREF =
>> B.LOGICALREF
>>                         LEFT JOIN L_CURRENCYLIST C ON A.TRCURR =
>> C.CURTYPE
>> WHERE A.TRCURR != 0
>> GROUP BY B.CODE, C.CURCODE
>>
>>
>> (SELECT RATES1 FROM L_DAILYEXCHANGES WHERE LREF = 1) ?????
>>
>> Thanks
>>
>>
>
>

14 Jul 2005 3:49 PM

Alejandro Mesa

Try,

SELECT
    B.CODE,
    C.CURCODE,
    SUM(A.AMOUNT) AS TL_TOPLAM,
    SUM(A.TRNET) AS DOVIZ_TOPLAM,
    SUM(A.TRNET * l.RATES1) AS FARK
FROM
    LG_001_01_CLFLINE A

    inner join

    (
    SELECT top 1 RATES1 FROM L_DAILYEXCHANGES WHERE LREF = 1
    ) AS L
    on 1 = 1

    LEFT JOIN
    LG_001_CLCARD B
    ON A.CLIENTREF = B.LOGICALREF
        LEFT JOIN
    L_CURRENCYLIST C
    ON A.TRCURR = C.CURTYPE
WHERE
    A.TRCURR != 0
GROUP BY
    B.CODE, C.CURCODE

-- or
-- use the appropiate data type
declare @v sql_variant

set @v = (SELECT top 1 RATES1 FROM L_DAILYEXCHANGES WHERE LREF = 1)

SELECT
    B.CODE,
    C.CURCODE,
    SUM(A.AMOUNT) AS TL_TOPLAM,
    SUM(A.TRNET) AS DOVIZ_TOPLAM,
    SUM(A.TRNET * @v) AS FARK
FROM
    LG_001_01_CLFLINE A
    LEFT JOIN
    LG_001_CLCARD B
    ON A.CLIENTREF = B.LOGICALREF
        LEFT JOIN
    L_CURRENCYLIST C
    ON A.TRCURR = C.CURTYPE
WHERE
    A.TRCURR != 0
GROUP BY
    B.CODE, C.CURCODE
go

AMB

Show quote

"Bongee" wrote:

> I have one row but the error message is
>
> Server: Msg 130, Level 15, State 1, Line 4
> Cannot perform an aggregate function on an expression containing an
> aggregate or a subquery.
>
> Problem?
>
>
> Thanks
>
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa>, haber iletisinde
> Ã¾unlarÃ½ yazdÃ½:uz3TQjIiFHA.1***@tk2msftngp13.phx.gbl...
> > Unless that table only ever has one row, you need to narrow it down to one
> > row by relating it to the rest of the query.
> >
> > Please see http://www.aspfaq.com/5006
> >
> >
> >
> > "Bongee" <bong***@bonbon.net> wrote in message
> > news:uipvshIiFHA.2156@TK2MSFTNGP14.phx.gbl...
> >> Hello,
> >>
> >> SELECT B.CODE, C.CURCODE,
> >>       SUM(A.AMOUNT) AS TL_TOPLAM,
> >>       SUM(A.TRNET) AS DOVIZ_TOPLAM,
> >>       SUM(A.TRNET * (SELECT RATES1 FROM L_DAILYEXCHANGES WHERE LREF =
> >> 1) ) AS FARK
> >> FROM LG_001_01_CLFLINE A LEFT JOIN LG_001_CLCARD B ON A.CLIENTREF =
> >> B.LOGICALREF
> >>                         LEFT JOIN L_CURRENCYLIST C ON A.TRCURR =
> >> C.CURTYPE
> >> WHERE A.TRCURR != 0
> >> GROUP BY B.CODE, C.CURCODE
> >>
> >>
> >> (SELECT RATES1 FROM L_DAILYEXCHANGES WHERE LREF = 1) ?????
> >>
> >> Thanks
> >>
> >>
> >
> >
>
>
>

14 Jul 2005 4:20 PM

--CELKO--

Without an DDL, we can only guess, but the subquery has to come out of
the SUM(), probably some thing like this:

SELECT B.foobar_code, C.cur_code,
       SUM(A.foobar_amount) AS tl_toplam,
       SUM(A.trnet) AS doviz_toplam,
       SUM(A.trnet)
        * (SELECT MAX(rates1)
             FROM DailyExchanges
            WHERE lref = 1) AS fark
FROM Lg_001_01_Clfline AS A
       LEFT OUTER JOIN
       Lg_001_Clcard AS B
       ON A.client_ref = B.logical_ref
          LEFT OUTER JOIN
          CurrencyList AS C
          ON A.tr_curr = C.cur_type
WHERE A.tr_curr <> 0
GROUP BY B.foobar_code, C.cur_code

You also need to go back and get usable data element name. There is no
such thing as just a "code" - it to be a code for something.
Likewise "amount" and so forth.

While I am sure that names like "Lg_001_01_Clfline" and
"Lg_001_Clcard" are meaningful to people in your trade, can you
tell me what logical (NOT sequential occurence in one query) reason
they will always read A as the universal abbreviation for
"Lg_001_01_Clfline" from query to query?

And why are you using uppercase? A video terminal is cheaper than
pounch cards and punch cards are the only reason for this.