CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2012
Faraz Ahmed
Faraz Ahmed

Reputation: 224

Query to get data with minimum query cost

I have a table, structure like this: 

   BID_FK               AccountID           Amount
    1                   1-1-1-1-1-1-1       4050
    1                   1-1-1-1-1-1-1       4050
    1                   1-1-1-1-1-1-1       4050
    1                   1-1-1-1-1-1-1       4050
    1                   1-1-1-1-1-1-2       500
    1                   1-1-1-1-1-1-2       500
    1                   1-1-1-1-1-1-2       500
    1                   1-1-1-1-1-1-2       500
    2                   1-1-1-1-1-1-1       6580
    2                   1-1-1-1-1-1-1       6580
    2                   1-1-1-1-1-1-1       6580
    2                   1-1-1-1-1-1-1       6580
    2                   1-1-1-1-2-1-1       1000
    2                   1-1-1-1-2-1-1       1000
    2                   1-1-1-1-2-1-1       1000
    2                   1-1-1-1-2-1-1       1000

I want to query for getting out put like this :

AccountID         Amount(BID_FK = 1)      Amount (BID_FK = 2)
1-1-1-1-1-1-1     16200                   26320
1-1-1-1-1-1-2     2000                    0
1-1-1-1-2-1-1     0                       4000

With minimum query cost because I have large number of data.

Any help would be highly appreciated!

Upvotes: 2

Views: 57

Answers (2)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

Maybe something like this could help?

SELECT
AccountID,
SUM(CASE WHEN BID_FK=1 THEN Amount ELSE 0 END) AMOUNT1,
SUM(CASE WHEN BID_FK=2 THEN Amount ELSE 0 END) AMOUNT2
FROM yourtable
GROUP BY AccountId

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

SELECT AccountID, 
       SUM( CASE WHEN BID_FK=1 THEN Amount ELSE 0 END) AS `Amount(BID_FK = 1)` 
       SUM( CASE WHEN BID_FK=2 THEN Amount ELSE 0 END) AS `Amount(BID_FK = 2)`
FROM yourTable
GROUP BY AccountID

Upvotes: 2

Related Questions