Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL SUM with GROUP BY Expand / Collapse
Author
Message
Posted Monday, April 7, 2008 3:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 4:32 PM
Points: 257, Visits: 535
Hello comunity

I have the following script :
select CREND = PL.CREND, NOME = PO.NOME, NCONT = PO.NCONT, EVIRS = sum(PL.EVIRS), EREC = sum(PL.EREC), U_TXIRS = isnull((select
isnull(fo.txirs,0) from fo (nolock) where fo.fostamp = PL.fcstamp),0), U_VLIRS = sum(isnull((select (case when isnull(fo.txirs,0) > 0 then PL.evirs/
isnull(fo.txirs,0) * 100 else 0 end) from fo (nolock) where fo.fostamp = pl.fcstamp),0)) from pl (nolock) inner join po (nolock) on
pl.postamp=po.postamp inner join fo on pl.fcstamp=fo.fostamp
where (convert(char(10),PL.RDATA,121) BETWEEN '2007-01-01' AND '2007-12-31' and PL.EVIRS > 0)

group by pl.crend,po.nome,po.ncont,pl.fcstamp

But , sql server return this error :
37000(130)[Microsoft][SQL Native Client][SQL Server]Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How can resolve this problem
Many thanks
Luis Santos



Post #480654
Posted Monday, April 7, 2008 3:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:41 AM
Points: 870, Visits: 894
I might be missing something here but I'm not sure you need those sub-queries there as you're alread joining on the table fo. See if this works:

select  CREND = PL.CREND, NOME = PO.NOME, NCONT = PO.NCONT, EVIRS = sum(PL.EVIRS), EREC = sum(PL.EREC), U_TXIRS = isnull(fo.txirs,0)
, U_VLIRS = sum(case when isnull(fo.txirs,0) > 0 then PL.evirs/ isnull(fo.txirs,0) * 100 else 0 end)
from pl (nolock)
inner join po (nolock) on pl.postamp=po.postamp inner join fo on pl.fcstamp=fo.fostamp
where convert(char(10),PL.RDATA,121) BETWEEN '2007-01-01' AND '2007-12-31'
AND PL.EVIRS > 0
group by pl.crend,po.nome,po.ncont,pl.fcstamp



Karl
source control for SQL Server
Post #480660
Posted Tuesday, April 8, 2008 3:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 4:32 PM
Points: 257, Visits: 535
Hello Karl
thanks for your reply, they work fine

bye
Luis Santos



Post #481904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse