create columns from transaction type in table

Question

I have an SQL query that groups by transaction type and then sums amount, but would like the summed amount to be in a column for each transaction type

i have this:

select Job, 
       sum(amount) as Amount, 
       transaction_type 
 from JCT_CURRENT__TRANSACTION
WHERE transaction_date >= {?Start Date} 
  and transaction_date <= {?End Date}
GROUP BY Job, transaction_type
ORDER BY Job ASC

But would like:

Job  | TransType1AmountSum  | TransType2AmountSum  |  TransType3AmountSum

so i would only have one line per job.

Answer 1

Use:

  SELECT t.job,
         SUM(CASE WHEN t.transaction_type = 'TransType1' THEN t.amount ELSE NULL END) AS TransType1AmountSum,
         SUM(CASE WHEN t.transaction_type = 'TransType2' THEN t.amount ELSE NULL END) AS TransType2AmountSum,
         SUM(CASE WHEN t.transaction_type = 'TransType3' THEN t.amount ELSE NULL END) AS TransType3AmountSum
    FROM JCT_CURRENT_TRANSACTION t
   WHERE transaction_date BETWEEN {?Start Date} 
                              AND {?End Date}
GROUP BY t.job

Answer 2

select Job, 
       sum(IF(transaction_type = 'transtype1', amount, 0)) as TransType1AmountSum, 
       ...,
       transaction_type 
 from JCT_CURRENT__TRANSACTION
WHERE transaction_date >= {?Start Date} 
  and transaction_date <= {?End Date}
GROUP BY Job

Answer 3

Here is another way to do it that should be faster (esp if you have an index on transaction type and are doing more than one operation -- like avg and count.)

This is in TSQL

SELECT Job.Job, 
       sum(T1.amount) as TransType1AmountSum, count(T1.amount) as Count1, avg(T1.amount) as Avg1
       sum(T2.amount) as TransType2AmountSum, count(T2.amount) as Count2, avg(T2.amount) as Avg2
       sum(T3.amount) as TransType3AmountSum, count(T3.amount) as Count3, avg(T3.amount) as Avg3
 FROM JCT_CURRENT__TRANSACTION AS Job
 JOIN JCT_CURRENT__TRANSACTION T1 ON Job.Job = T1.Job AND T1.TransactionType = 'TransType1'
 JOIN JCT_CURRENT__TRANSACTION T2 ON Job.Job = T2.Job AND T2.TransactionType = 'TransType2'
 JOIN JCT_CURRENT__TRANSACTION T3 ON Job.Job = T3.Job AND T3.TransactionType = 'TransType3'
WHERE Job.transaction_date >= {?Start Date} 
  and Job.transaction_date <= {?End Date}
GROUP BY Job.Job