CODEWITHSUNDEEP
mysqlsql
Chase W.
Chase W.

Reputation: 1413

Adding a "total sum" row with a select statement in Mysql

Query:

SELECT   (CASE
           WHEN a.account_number = '123456' THEN 'Savings'
           WHEN a.account_number = '123321' THEN 'Credit'
         END) AS "Account"
FROM accounts a
GROUP BY (CASE
           WHEN a.account_number = '123456' THEN 'Savings'
           WHEN a.account_number = '123321' THEN 'Credit'
         END);

Output:

+---------+
| Account |
+---------+
| Savings |
| Credit  |
+---------+

Desired Output:

+---------+
| Account |
+---------+
| Savings |
| Credit  |
| Total   |
+---------+

Given this query, how can I add a new row with the totals on the bottom? Later I'm going to be doing more transactions...

Upvotes: 1

Views: 3105

Answers (2)

Luke Cousins
Luke Cousins

Reputation: 2156

Presumably Savings == Debit?

It might well be easier to do this in the programming language rather than the query, but could you try something like:

SELECT   IFNULL((CASE
           WHEN a.account_number = '123456' THEN 'Savings'
           WHEN a.account_number = '123321' THEN 'Credit'
         END), "Total") AS "Account"
FROM accounts a
GROUP BY a.account_number WITH ROLLUP;

I've not tried this yet so not 100% certain it will work, or how to make it named "Total" (it will be under NULL).

See WITH ROLLUP.

Upvotes: 3

rmon2852
rmon2852

Reputation: 220

Looking at your diagram is it a total row that you're after? I'm a little confused.

However, if it is a total row adding all credits and debits together then i would look into the UNION statement.

basic example:

SELECT a.AccountCreditsDebits From Table
UNION
SELECT SUM(a.AccountCreditsDebits) From Table

Upvotes: 0

Related Questions