Untuk Om Mizno

mizno.txt

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test;
Database changed
mysql> select * from mizno_items;
+----+--------+
| id | NAME   |
+----+--------+
|  1 | item 1 |
|  2 | item 2 |
|  3 | item 3 |
|  4 | item 4 |
|  5 | item 5 |
+----+--------+
5 rows in set (0.00 sec)

mysql> select * from mizno_transaksi;
+---------+------+------------+----------+
| item_id | tipe | tanggal    | quantity |
+---------+------+------------+----------+
|       1 | in   | 2012-08-07 |       80 |
|       2 | in   | 2012-08-07 |       50 |
|       3 | in   | 2012-08-07 |       30 |
|       4 | in   | 2012-08-07 |       70 |
|       5 | in   | 2012-08-07 |       90 |
|       2 | out  | 2012-08-07 |      -10 |
|       4 | out  | 2012-08-07 |      -30 |
|       5 | out  | 2012-08-07 |      -20 |
|       1 | in   | 2012-08-08 |       80 |
|       2 | in   | 2012-08-08 |       50 |
|       4 | in   | 2012-08-08 |       70 |
|       5 | in   | 2012-08-08 |       90 |
|       1 | out  | 2012-08-08 |      -10 |
|       1 | out  | 2012-08-08 |      -10 |
|       3 | out  | 2012-08-08 |      -30 |
|       4 | out  | 2012-08-08 |      -20 |
+---------+------+------------+----------+
16 rows in set (0.00 sec)

mysql> select tanggal
    -> , sum(case when id=1 then quantity else 0 end) item1
    -> , sum(case when id=2 then quantity else 0 end) item2
    -> , sum(case when id=3 then quantity else 0 end) item3
    -> , sum(case when id=4 then quantity else 0 end) item4
    -> , sum(case when id=5 then quantity else 0 end) item5
    -> from mizno_transaksi a
    -> join mizno_items b on a.item_id=b.id
    -> group by tanggal;
+------------+-------+-------+-------+-------+-------+
| tanggal    | item1 | item2 | item3 | item4 | item5 |
+------------+-------+-------+-------+-------+-------+
| 2012-08-07 |    80 |    40 |    30 |    40 |    70 |
| 2012-08-08 |    60 |    50 |   -30 |    50 |    90 |
+------------+-------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

mysql> select id, name
    -> , sum(case when tanggal='2012-08-07' then quantity else 0 end) "2012-08-07"
    -> , sum(case when tanggal='2012-08-08' then quantity else 0 end) "2012-08-08"
    -> from mizno_transaksi a
    -> join mizno_items b on a.item_id=b.id
    -> group by id, name;
+----+--------+------------+------------+
| id | name   | 2012-08-07 | 2012-08-08 |
+----+--------+------------+------------+
|  1 | item 1 |         80 |         60 |
|  2 | item 2 |         40 |         50 |
|  3 | item 3 |         30 |        -30 |
|  4 | item 4 |         40 |         50 |
|  5 | item 5 |         70 |         90 |
+----+--------+------------+------------+
5 rows in set (0.00 sec)

mysql>