Help with Query looking for single record to be returned, but getting 1

[awholtsIT]

I wrote the following query wanting the results to be one row with a sum for column1 and column2 as the fields. However,
I'm getting 20 records results with a sum by the field ledgertrans.accountnum.

Appreciate some assistance with getting the desired result.

Thank you,

Andrew

++++++++++++++++++++++++++
select
(select sum(ledgertrans.amountcur)
where ledgertrans.accountnum in
('41010','41011','42010','42020','42030','42080','42310'))
as [Column 1],
(select sum(ledgertrans.amountcur)
where ledgertrans.accountnum in
('51010','51015','51020','51030','52010','52020','52050','52051','53020','53030','53040','53050','53090'))
as [Column 2]
from ledgertrans
where ledgertrans.transdate = '2009-11-02'
and ledgertrans.dimension = 'ws'
group by ledgertrans.accountnum

 

[markros]

Remove GROUP BY from your query to get only one record with SUMs.

I don't even understand how the query you wrote works, it seems to be incorrect.

Also, you may re-write your query to be more efficient this way

select sum(case when AccountNum in (...) then AmountCur end)) as FirstSum, sum(case when AccountNum in (..) then AmountCur end)) as SecondSum from 
ledgertrans
where ledgertrans.transdate = '2009-11-02'    and ledgertrans.dimension = 'ws'

 

[bborissov]

select 
     SUM(CASE WHEN ledgertrans.accountnum in ('41010','41011','42010','42020','42030','42080','42310')
              THEN ledgertrans.amountcur ELSE 0 END) as [Column 1],
     SUM(CASE WHEN ledgertrans.accountnum in ('51010','51015','51020','51030','52010','52020','52050','52051','53020','53030','53040','53050','53090')
              THEN ledgertrans.amountcur ELSE 0 END) as [Column 2]
from ledgertrans
where ledgertrans.transdate = '2009-11-02'
  and ledgertrans.dimension = 'ws'
  AND ledgertrans.accountnum in ('41010','41011','42010','42020','42030','42080','42310','51010','51015','51020','51030','52010','52020','52050','52051','53020','53030','53040','53050','53090'
group by ledgertrans.accountnum

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[awholtsIT]

Thank you,

That is exactly what i'm looking to accomplish.

Thanks again,

Andrew

 

[bborissov]

I even didn't notice that there is no other fields in query aside the SUMs

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Hi Boris,

Good point about adding the WHERE condition - I forgot to add it.

You don't need GROUP BY in your query, otherwise you also get as many records as individual AccountNums