CODEWITHSUNDEEP
mysql
Abdul Manaf
Abdul Manaf

Reputation: 4888

MySQL Query output using if else or case..?

I have a table as 

mysql> select * FROM testa;
+---------+-------+
| month_x | money |
+---------+-------+
| 11101   | 12345 |
| 11105   |   100 |
| 11105   |   100 |
| 11105   |   100 |
| 11105   |   100 |
| 11106   | 12345 |
+---------+-------+
6 rows in set (0.00 sec)

where last two digits in the month_x are months now i want my output as

Month   TOTAL
01  12345
02  0
03  0
04  0
05  400
06  12345
07  0
08  0
09  0
10  0
11  0
12  0

IS possible using the If else or case.

Upvotes: 0

Views: 312

Answers (3)

eggyal
eggyal

Reputation: 125945

You can use modular arithmetic to obtain the trailing two digits (they're the remainder when the number is divided by 100), then assuming you wish to sum money when your data is "grouped by" month:

SELECT month_x % 100 AS Month, SUM(money) AS TOTAL
FROM testa
GROUP BY Month
ORDER BY Month ASC;

Alternatively, you could use rely on MySQL's implicit type conversion and use its string functions:

SELECT RIGHT(month_x, 2) AS Month, SUM(money) AS TOTAL
FROM testa
GROUP BY Month
ORDER BY Month ASC;

UPDATE

As @shiplu.mokadd.im states, to show every month (even those for which you have no data), you need to obtain numbers 1 through 12 from a temporary table. However, you can create such a temporary table in your query using UNION:

      SELECT 1
UNION SELECT 2
UNION SELECT 3 -- etc

Therefore:

SELECT Month, Sum(money) AS TOTAL
FROM   testa
  RIGHT JOIN (
        SELECT  1 AS Month
  UNION SELECT  2 UNION SELECT  3 UNION SELECT  4 UNION SELECT  5 UNION SELECT  6
  UNION SELECT  7 UNION SELECT  8 UNION SELECT  9 UNION SELECT 10 UNION SELECT 11
  UNION SELECT 12
  ) months ON testa.month_x % 100 = months.Month
GROUP BY Month;

HOWEVER I would note that usually one doesn't usually do this in the database, as it really belongs in the presentation layer: from whatever language you're accessing the database, you'd loop over 1...12 and assume TOTAL to be 0 if there's no corresponding record in the resultset.

Upvotes: 2

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57670

For this you need to create a table first with months' numeric value in it. 

CREATE TABLE `months` (
  `mon` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO `months` VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);

Then execute this query,

SELECT m.mon, 
       IF(Sum(t.money) IS NULL, 0, Sum(t.money)) AS `money` 
FROM   testa t 
       RIGHT OUTER JOIN months m 
         ON ( t.month_x%100 = m.mon ) 
GROUP  BY m.mon;

Result is, 

+------+-------+
| mon  | money |
+------+-------+
|    1 | 12345 |
|    2 |     0 |
|    3 |     0 |
|    4 |     0 |
|    5 |   400 |
|    6 | 12345 |
|    7 |     0 |
|    8 |     0 |
|    9 |     0 |
|   10 |     0 |
|   11 |     0 |
|   12 |     0 |
+------+-------+

Upvotes: 1

Bonny Bonev
Bonny Bonev

Reputation: 131

You can use IF statements - yes. Look @ this: http://dev.mysql.com/doc/refman/5.5/en/if-statement.html

Upvotes: 0

Related Questions