CODEWITHSUNDEEP
mysql
Peon
Peon

Reputation: 8020

Summing multiple ORDER BY columns

I created this question, witch is now taken care of, but I have another struggle with the same query:

SELECT
  t.type,
  SUM( t.external_account )
FROM
  u_contracts c,
  u_data u,
  u_transactions t
WHERE
  c.user_id = u.id
  AND t.contract_id = c.id
  AND t.nulled =0
  AND DATE (c.init_date) < DATE (u.dead)
  AND u.dead IS NOT NULL
  AND t.type != 'repay'
GROUP BY
  t.type
ORDER BY
  FIELD( t.type, 'initial', 'comission', 'overpay', 'penalty', 'penalty2' );

The results I get are as follows:

+-----------+---------------------------+
| type      | SUM( t.external_account ) |
+-----------+---------------------------+
| prolong   |                 360560.00 |
| reg       |                   3889.00 |
| reg2      |                    301.20 |
| initial   |                 610628.54 |
| comission |                 125623.49 |
| overpay   |                   6461.57 |
| penalty   |                  21461.52 |
| penalty2  |                   4010.00 |
+-----------+---------------------------+

I need to SUM up results that have type prolong + reg + reg2 and add them to the end of the list.

Now I have absolutely no idea how sum them, considering, that they come as a result from GROUP BY t.type request.

Upvotes: 1

Views: 55

Answers (1)

Vikdor
Vikdor

Reputation: 24134

Give this a try:

SELECT 
    t.type,
    SUM(t.external_account)
FROM
(
    SELECT
      CASE t.type
        WHEN 'prolong' THEN 'prolong + reg + reg2'
        WHEN 'reg' THEN 'prolong + reg + reg2'
        WHEN 'reg2' THEN 'prolong + reg + reg2'
        ELSE t.type
      END AS type,
      t.external_account
    FROM
      u_contracts c,
      u_data u,
      u_transactions t
    WHERE
      c.user_id = u.id
      AND t.contract_id = c.id
      AND t.nulled =0
      AND DATE (c.init_date) < DATE (u.dead)
      AND u.dead IS NOT NULL
      AND t.type != 'repay'
) t 
GROUP BY
      t.type
ORDER BY
  FIELD( t.type, 'initial', 'comission', 'overpay', 'penalty', 'penalty2' );

Upvotes: 3

Related Questions