Credit debit

From SQLZOO
Revision as of 14:57, 2 August 2012 by Connor (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

In this example you are shown how to split a single column into two separate columns and also below you are

told how to combine two tables into a single table. Here we are splitting cash amounts into credit and debit.

Table 1 shows the results without the split column and Table 2 shows what we get when the column is split into two.

Table 1
whndescriptionamount
2006-11-01Wages50
2006-11-02Company Store-10
2006-11-03Company Store-10
2006-11-04Company Store-10
2006-11-05Company Store-10
2006-11-06Company Store-10
Table 2
whndescriptioncshINcshOUT
2006-11-01Wages50
2006-11-02Company Store10
2006-11-03Company Store10
2006-11-04Company Store10
2006-11-05Company Store10
2006-11-06Company Store10
schema:scott
DROP TABLE transact
 CREATE TABLE transact(
  whn DATE,
  description VARCHAR(20),
  amount INTEGER );
INSERT INTO transact VALUES ('2006-11-01','Wages',50);
INSERT INTO transact VALUES ('2006-11-02','Company Store',-10);
INSERT INTO transact VALUES ('2006-11-03','Company Store',-10);
INSERT INTO transact VALUES ('2006-11-04','Company Store',-10);
INSERT INTO transact VALUES ('2006-11-05','Company Store',-10);
INSERT INTO transact VALUES ('2006-11-06','Company Store',-10);
SELECT w AS dte, d AS description, 
   CASE WHEN (a>=0) THEN a ELSE NULL END AS cshIN,
   CASE WHEN (a<0) THEN SUBSTR(a,1,10) ELSE NULL END AS cshOUT
  FROM
  (SELECT x.whn AS w, x.description AS d, 
          x.amount AS a
     FROM transact x
     JOIN transact y ON (x.whn>=y.whn)
     GROUP BY x.whn, x.description, x.amount) t
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense