Number of SQL queries in a column

Question

Using MySQL 5 with the "abrechnung" Table

id   datum     title   betrag  wal jon  al  ben
7   2010-02-08  Essen   1362     0   0   1   0
8   2010-02-15  Essen   324      0   0   1   2
9   2010-02-15  Essen   5732     0   0   1   2
10  2010-02-15  Essen   3245     0   2   1   2 

In the end I want: each line of "betrag" is divided by a number and then added to the final result. The number that is divided is 4 - the number 2 in the current line.

My current approach is as follows:

SELECT SUM(betrag) AS "W->A" FROM abrechnung WHERE (wal = "0" and al = "1");

Mostly the records that I want to add to the final result are selected. The result in this case: 10663.

In fact, the end result will be:

  1362 / 4 (no number 2 in that row for wal, jon, al or ben)
+  324 / 3 (there is one 2 in that row for ben)
+ 5732 / 3 (same)
+ 3245 / 2 (there are 2 2 in that row)
-----------
  3 981.66

Hope this is understandable.

Thank.

Answer 1

SELECT SUM(betrag / (
 4-CASE WHEN wal = 2 THEN 1 ELSE 0 END
  -CASE WHEN jon = 2 THEN 1 ELSE 0 END
  -CASE WHEN al  = 2 THEN 1 ELSE 0 END
  -CASE WHEN ben = 2 THEN 1 ELSE 0 END
))
AS "W->A" FROM abrechnung