SQL Sums in Rows Pulled out into Columns?

Question

I have a search that is very slowly done by finding the primary records then looping in a thread to sum related transactions. I'm trying to make it work in a single statement, have come close but still have records which are alternating debit and credits.

I can't work out how to pull the debit and credit rows up into columns so there is one row of results per date and jobkey.

SELECT j.dtmInvoicedOn, j.strJobKey, c.strCustName, strTransType, 
     SUM(r.dblTransactionAmount) AS SUM_dblTotalCharge 
     FROM tbljobs AS j
     INNER JOIN tblreceivledger AS r ON j.strJobKey = r.strJobKey 
     INNER JOIN tblcustomers AS c ON j.intCustomerID = c.intCustomerID 
     WHERE c.strCustomerName = 'Acme Runners Inc'
     GROUP BY j.strJobKey, c.strCustName, strTransType
     ORDER BY dtmInvoicedOn, strJobKey;

produces output like this, with nearly alternating debit and credit sums

+----------------+---------------+------------------+--------------------+--------------------+
| dtmInvoicedOn  | strJobKey     | strCustomerName  | strTransactionType | SUM_dblTotalCharge |
+----------------+---------------+------------------+--------------------+--------------------+
| 2008-07-03     | 270876-1      | Acme Runners Inc | credit             |           -5531.52 | 
| 2008-07-11     | 270880-1      | Acme Runners Inc | debit              |            5058.54 | 
| 2008-07-11     | 270880-1      | Acme Runners Inc | credit             |           -5058.54 | 
| 2008-07-18     | 271468-1      | Acme Runners Inc | debit              |            5290.17 | 
| 2008-07-18     | 271468-1      | Acme Runners Inc | credit             |           -5290.17 | 
| 2008-11-07     | 286049-1      | Acme Runners Inc | debit              |            5230.44 | 
| 2008-11-14     | 286051-1      | Acme Runners Inc | debit              |            5375.14 | 
| 2008-11-21     | 286107-1      | Acme Runners Inc | debit              |            5572.33 | 
| 2008-11-28     | 286112-1      | Acme Runners Inc | debit              |            5123.42 |

so I want it to look like:

+----------------+---------------+------------------+----------+----------+
| dtmInvoicedOn  | strJobKey     | strCustomerName  |   credit |    debit |
+----------------+---------------+------------------+----------+----------+
| 2008-07-03     | 270876-1      | Acme Runners Inc | -5531.52 |        0 |
| 2008-07-11     | 270880-1      | Acme Runners Inc | -5058.54 |  5058.54 | 
| 2008-07-18     | 271468-1      | Acme Runners Inc | -5290.17 |  5290.17 | 
| 2008-11-07     | 286049-1      | Acme Runners Inc |        0 |  5230.44 | 
| 2008-11-14     | 286051-1      | Acme Runners Inc |        0 |  5375.14 | 
| 2008-11-21     | 286107-1      | Acme Runners Inc |        0 |  5572.33 | 
| 2008-11-28     | 286112-1      | Acme Runners Inc          0 |  5123.42 |

Note that the server is currently running mysql but will migrate to postgres and sqlite later.

thanks

Answer 1

this should do the job:

SELECT j.dtmInvoicedOn, j.strJobKey, c.strCustName, strTransType, 
     SUM(CASE WHEN strTransType='credit' THEN r.dblTransactionAmount ELSE 0 END) AS SUM_CREDIT,
     SUM(CASE WHEN strTransType='debit' THEN r.dblTransactionAmount ELSE 0 END) AS SUM_DEBIT
FROM tbljobs AS j
     INNER JOIN tblreceivledger AS r ON j.strJobKey = r.strJobKey 
     INNER JOIN tblcustomers AS c ON j.intCustomerID = c.intCustomerID 
WHERE c.strCustomerName = 'Acme Runners Inc'
GROUP BY j.strJobKey, c.strCustName
ORDER BY dtmInvoicedOn, strJobKey;

Answer 2

You want to get rid of transaction type and have two fields that total depending on being a debit or credit.

SELECT j.dtmInvoicedOn
, j.strJobKey
, c.strCustName
, Sum(Case When strTransactionType = 'credit' then r.dblTransactionAmount else 0 end) as credit
, Sum(Case When strTransactionType = 'debit' then r.dblTransactionAmount else 0 end) as debit   
FROM tbljobs AS j     
INNER JOIN tblreceivledger AS r ON j.strJobKey = r.strJobKey      
INNER JOIN tblcustomers AS c ON j.intCustomerID = c.intCustomerID      
WHERE c.strCustomerName = 'Acme Runners Inc'     
GROUP BY j.dtmInvoicedOn
, j.strJobKey
, c.strCustName
ORDER BY dtmInvoicedOn, strJobKey;