CODEWITHSUNDEEP
sql
Melz
Melz

Reputation: 13

SQL how to get the sum of fields to a specific value and make it a column header?

for example: i have a table like this:

    ------------------------------------
    ID | Name | Color | Account | Amt  |
    1  | Mark | Red   | 001-002 | 5.20 |
    2  | Lee  | Blue  | 001-002 | 4.50 |
    3  | Mel  | Blue  | 001-003 | 3.00 |
    4  | Joe  | Green | 001-001 | 2.00 |
    5  | Mike | Blue  | 001-002 | 1.00 |
    ------------------------------------

how to make it look like this:?

--------------------------------
 Account | Red  | Blue | Green |
 001-001 | 0.00 | 0.00 | 2.00  |
 001-002 | 5.20 | 5.50 | 0.00  |
 001-003 | 0.00 | 3.00 | 0.00  |
--------------------------------

Upvotes: 0

Views: 233

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

If you have the specific list of colors, you can use conditional aggregation:

select account,
       sum(case when color = 'blue' then amt else 0 end) as blue,
       sum(case when color = 'red' then amt else 0 end) as red,
       sum(case when color = 'green' then amt else 0 end) as green
from mytable
group by Account;

If you don't have the list, then you may need to look at dynamic SQL.

EDIT:

In MS Access, you can do the same idea using iif():

select account,
       sum(iif(color = 'blue', amt, 0) as blue,
       sum(iif(color = 'red', amt, 0) as red,
       sum(iif(color = 'green', amt, 0) as green
from mytable
group by Account;

Upvotes: 2

D-Shih
D-Shih

Reputation: 46249

You can try to use PIVOT

SELECT Account,
      coalesce([Red],0) 'Red',
      coalesce([Blue],0) 'Blue',
      coalesce([Green],0) 'Green'
FROM (
   SELECT Account,color,SUM(Amt) Amt
   FROM T
   group by Account,color
) t
PIVOT(
  SUM(t.Amt) FOR color IN ([Red],[Blue],[Green])
)p1

sqlfiddle:http://sqlfiddle.com/#!18/699a6/4

Upvotes: 0

Related Questions