What is best way to write query with multiple aggregate columns and multiple where clause

Question

I have a table tbl_LEDGER

+-----------+--------+-----+
| AccountId | GlCode | Amt |
+-----------+--------+-----+
| LAS00001  | INTRAC | 100 |
| LAS00002  | INTRAC | 150 |
| LAS00001  | INTLAS | 200 |
+-----------+--------+-----+

Desired Result :

+-----------+------------+-----------+
| AccountId | intractamt | intlasAmt |
+-----------+------------+-----------+
| LAS00001  |        100 |       200 |
| LAS00002  |        150 |         0 |
+-----------+------------+-----------+

Here is my working query:

select accountid,sum(amt) intracamt,  (select SUM(amt)  from tbl_LEDGER 
where   GLCode='intlas' and AccountID=intrac.AccountID ) intlasamt 
from tbl_LEDGER intrac 
where GLCode='intrac'    group by AccountID    order by AccountID

Another Working query :

select a.accountId,a.amt as 'RACAMT',b.amt as 'LACAMT' from 
(
select accountid ,glcode, SUM(amt) as amt from nbfcledger where GLCode='intrac' group by GLCode,AccountID 
) a
inner join 
(
select accountid ,glcode, SUM(amt) as amt from nbfcledger where GLCode='intlas'  group by GLCode,AccountID 
)b
on a.AccountID = b.AccountID order by AccountID

What are the other ways in which I can achieve the same result? Which one is best and why? I was hoping I could do this without PIVOT.

Which version of SQL-Server? Are there more differing values in GlCode? — Shnugo, Dec 28 '15 at 10:48
@Shnugo I am using SQL Server 2008, yes there are more differing values in GlCode. Bu I am interested in just these two. — SamuraiJack, Dec 28 '15 at 10:49
@sstan other than the fact that I already know that way and I dont like the syntax. No. If you want to post pivot as answer go ahead. It might help someone. If it is most efficient way. I would mark it as answer. — SamuraiJack, Dec 28 '15 at 11:35
Possible duplicate of [Where clause condition on aggregate functions](http://stackoverflow.com/questions/4662955/where-clause-condition-on-aggregate-functions) — Steven, Dec 28 '15 at 14:46

score 5 · Answer 1 · answered Dec 28 '15 at 10:43

5

select AccountId,
       Sum(case when GlCode = 'INTRAC' then amt else 0 end ) as intractamt,
       Sum(case when GlCode = 'INTLAS' then amt else 0 end ) as intlasAmt 
from tbl_LEDGER
group by AccountId

Another way to do the same is as given above.

answered Dec 28 '15 at 10:43

Akshey Bhat

8,227
1
20
20

definitely more efficient than the queries posted in the question. – sstan Dec 28 '15 at 11:27
@sstan I don't think so because above query takes around 5 seconds on my table and the one provided by me `select accountid,sum(amt) intracamt, (select SUM(amt) from tbl_LEDGER where GLCode='intlas' and AccountID=intrac.AccountID ) intlasamt from tbl_LEDGER intrac where GLCode='intrac' group by AccountID order by AccountID` takes less than 1 second. Unless my query is returning result from cache – SamuraiJack Dec 28 '15 at 12:30
If `GLCode` has more values than just `INTRAC` and `INTLAS`, and depending on whether that column has an index, and depending on the values distribution of that column, it could be that the query needs a `WHERE` clause as well (`where GLCode in ('INTRAC', 'INTLAS')`). Adding that filter certainly wouldn't hurt, and it could definitely help. This is the only detail that would explain it being slower than the queries posted in the question, imo. – sstan Dec 28 '15 at 13:14

score 0 · Answer 2 · answered Dec 28 '15 at 10:55

This is my approach:

 DECLARE @tbl_LEDGER TABLE(AccountId VARCHAR(100),GlCode VARCHAR(100),Amt DECIMAL(8,4));
 INSERT INTO @tbl_LEDGER VALUES
 ('LAS00001','INTRAC',100)
,('LAS00002','INTRAC',150)
,('LAS00001','INTLAS',200);

WITH IntracValues AS
(
    SELECT * 
    FROM @tbl_LEDGER
    WHERE GlCode='INTRAC'
)
,IntlasValues AS
(
    SELECT * 
    FROM @tbl_LEDGER
    WHERE GlCode='INTLAS'
)
SELECT IntracValues.AccountId
      ,ISNULL(IntracValues.Amt,0) AS intractAmt
      ,ISNULL(IntlasValues.Amt,0) AS intlasAmt
FROM IntracValues 
FULL OUTER JOIN IntlasValues ON IntracValues.AccountId=IntlasValues.AccountId

score 0 · Answer 3 · answered Dec 28 '15 at 13:03

Could you check this:

WITH DataSource ([AccountId], [Type], [Amount]) AS
(
    SELECT [AccountId]
           ,'intractamt'
           ,Sum(amt)
    FROM tbl_LEDGER
    WHERE [GlCode] = 'INTRAC'
    GROUP BY AccountId 
    UNION ALL
    SELECT AccountId
          ,'intlasAmt'
          ,Sum(amt) 
    FROM tbl_LEDGER
    WHERE [GlCode] = 'INTLAS'
    GROUP BY [AccountId]
)
SELECT [AccountId]
      ,[intractamt]
      ,[intlasAmt]
FROM DataSource
PIVOT
(
    MAX([Amount]) FOR [Type] IN ([intractamt], [intlasAmt])
) PVT

If you are not testing on production execute the queries with:

DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;

and use the following site to compare the statistics from the message tab.

What is best way to write query with multiple aggregate columns and multiple where clause

3 Answers3