in een query rekenen?

<?php
require_once './inc/dev.php'; // error reporting, debug functions, headers, database connection

$db->query('DROP TABLE IF EXISTS sumtest');
$db->query(
    'CREATE TABLE sumtest (
        id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
        income BOOL NOT NULL DEFAULT 0,
        amount INT(10) NOT NULL DEFAULT 0
    ) ENGINE=InnoDb DEFAULT CHARSET=utf8'
);
$db->query('TRUNCATE sumtest');

$items = 5;
for ($i=0; $i < $items; $i++) {
    $db->query(
        "INSERT INTO sumtest (income, amount) VALUES (
            '".$db->real_escape_string(rand(0, 1))."',
            '".$db->real_escape_string(rand(1, 5000))."'
        )"
    );
}

// set #1 after running script once
/*
> SELECT * FROM sumtest;
+----+--------+--------+
| id | income | amount |
+----+--------+--------+
|  1 |      1 |   1479 |
|  2 |      1 |   2066 |
|  3 |      0 |   2100 |
|  4 |      0 |   1975 |
|  5 |      0 |   2137 |
+----+--------+--------+
5 rows in set (0.00 sec)

> SELECT SUM(IF(income, 1, -1) * amount) AS total FROM sumtest;
+-------+
| total |
+-------+
| -2667 |
+-------+
1 row in set (0.00 sec)
*/
// set #2 after running script again
/*
> SELECT * FROM sumtest;
+----+--------+--------+
| id | income | amount |
+----+--------+--------+
|  1 |      0 |   1243 |
|  2 |      1 |   4845 |
|  3 |      1 |   3521 |
|  4 |      1 |    860 |
|  5 |      0 |   2134 |
+----+--------+--------+
5 rows in set (0.00 sec)

> SELECT SUM(IF(income, 1, -1) * amount) AS total FROM sumtest;
+-------+
| total |
+-------+
|  5849 |
+-------+
*/
?>[end]

fillToZero: true

<?php
error_reporting(E_ALL);
ini_set('display_errors', 'stdout');
date_default_timezone_set("Europe/Amsterdam");
setlocale( LC_ALL, array( 'nl_NL.UTF-8', 'nld_NLD.UTF-8', 'nld@euro', 'nl@euro', 'dutch', 'Dutch_Netherlands' ) );
//setlocale(LC_MONETARY,'nl_NL.UTF-8');
include("databaseopenen.php");
include("administratieopenen.php");
include("controlefuncties.php");

if (isset($_GET['id'])){
    // administratie id is altijd nodig
    // via url opgeroepen en is altijd groter of kleiner dan 0
    $adm_id=$_GET['id'];
} else {
    // Nieuw record is altijd leeg of 0
    // Deze kan niet leeg of 0 zijn. Want dan zou er geen administratie zijn.
    // Een administratie moet er altijd zijn om een dagboek te maken.
    $errors_programmafout[] = "Er is geen administratie meegegeven!";
}

/*    
    Hier onder staan de opties dd1 en dd2
    dd1 is datum1 voor het maken van een selectie
    dd2 is datum2 voor het maken van een selectie
*/
if (isset($_GET['dd1'])) { // via url opgeroepen dd1 is meegegeven
    // datum1 is nodig voor de grafieken
    // via url opgeroepen
    $dagb_datum1=$_GET['dd1'];
    // Voor het database gebruik moet de datum omgedraaid zijn.
    // Y-m-d
    $datum1 = substr($dagb_datum1, 6,4)."-".substr($dagb_datum1, 3,2)."-".substr($dagb_datum1,0,2);
} else {
    $errors_programmafout[] = "Er is geen datum 1 meegegeven!";    
}
if (isset($_GET['dd2'])) { // via url opgeroepen dd2 is meegegeven
    // datum1 is nodig voor de grafieken
    // via url opgeroepen
    $dagb_datum2=$_GET['dd2'];
    // Voor het database gebruik moet de datum omgedraaid zijn.
    // Y-m-d
    $datum2 = substr($dagb_datum2, 6,4)."-".substr($dagb_datum2, 3,2)."-".substr($dagb_datum2,0,2);
} else {
    $errors_programmafout[] = "Er is geen datum 2 meegegeven!";    
}

// indien het formulier verstuurd is
if($_SERVER['REQUEST_METHOD'] == 'POST') {
    
    // overschrijf de standaardwaarden voor de waarden die ge-POST zijn
    $dagb_datum1 = date_format(new DateTime( $_POST["dagb_datum1"]), 'd-m-Y');
    $datum1 = substr($dagb_datum1, 6,4)."-".substr($dagb_datum1, 3,2)."-".substr($dagb_datum1,0,2);
    $dagb_datum2 = date_format(new DateTime( $_POST["dagb_datum2"]), 'd-m-Y');
    $datum2 = substr($dagb_datum2, 6,4)."-".substr($dagb_datum2, 3,2)."-".substr($dagb_datum2,0,2);
    
}
    
if ($dagb_datum1===$dagb_datum2) {
    $melding="Grafiek over ".$dagb_datum1;
} else {
    $melding="Grafiek over ".$dagb_datum1." t/m ".$dagb_datum2;
}

// ******** Hier volgt de query voor de grafiek **********
$query_journaalposten_totaal = "SELECT journaalposten_datum, sum(journaalposten_bedrag*(if (journaalposten_inkomsten,1,-1))) as totaal_bedrag FROM tabel_journaalposten
                                WHERE journaalposten_administratie = ?
                                AND journaalposten_datum BETWEEN ? AND ?
                                GROUP BY journaalposten_datum";
    

if ($stmt_journaalposten_totaal = $dbh->prepare($query_journaalposten_totaal)){
    /*
        Binds variables to prepared statement

        i    corresponding variable has type integer
        d    corresponding variable has type double
        s    corresponding variable has type string
        b    corresponding variable is a blob and will be sent in packets
    */
    $stmt_journaalposten_totaal->bind_param("iss", $adm_id, $datum1, $datum2);

    /* execute query */
    $stmt_journaalposten_totaal->execute();

    /* Store the result (to get properties) */
    $stmt_journaalposten_totaal->store_result();

    /* Get the number of rows */
    $num_of_rows = $stmt_journaalposten_totaal->num_rows;

    /* Bind the result to variables */
    $stmt_journaalposten_totaal->bind_result($journaalposten_datum, $totaal_bedrag);
 
    while ($line = $stmt_journaalposten_totaal->fetch()) {  
        $journaalposten[] = array($journaalposten_datum, $totaal_bedrag);
    }
    
    /* free results */
    $stmt_journaalposten_totaal->free_result();

    /* close statement */
    $stmt_journaalposten_totaal->close();
} else {
    echo "Prepare failed: (" . $dbh->errno . ") " . $dbh->error;
}

header('ContentType: application/json; charset=utf-8');

// ******** Einde query voor de grafiek **********



//knop om naar Dagboeken.php te gaan
$linkdagboek="dagboeken.php?id=".$adm_id."&dd1=".$dagb_datum1."&dd2=".$dagb_datum2;
$linkdagboek_knop_lijst="<a href=".$linkdagboek."><img type='button' src='images/leeg.png' class='button_lijst' alt='dagboeken'></a>";

$homelink="index.php?id=".$adm_id;
$homeuit="";
$homeknop="<a href=".$homelink."><img src='images/leeg.png' class='button_home' alt='Basispagina van deze administratie'></a>";
                            
$verwijderlink="";
$verwijderuit="disabled";
$verwijderknop="<button type='submit' src='images/leeg.png' class='button_delete' alt='Verwijderen' ".$verwijderuit."></button>";

$bewerklink="";
$bewerkuit="disabled";
$bewerkknop="<button type='submit' src='images/leeg.png' class='button_edit' alt='Bewerken' ".$bewerkuit."></button>";

$lijstlink="";
$lijstuit="disabled";
$lijstknop="<button type='submit' src='images/leeg.png' class='button_lijst' alt='Lijst' ".$lijstuit."></a>";

$toevoeglink="";
$toevoeguit="disabled";
$toevoegknop="<button type='submit' src='images/leeg.png' class='button_add' alt='Toevoegen' ".$toevoeguit."></button>";

?>
<!DOCTYPE html>                
<html lang='nl'>
<head>
    <meta charset="utf-8">
    <title>Geldboom grafiek</title>
    <link rel="stylesheet" type="text/css" href="css/style.css">
    <link rel="stylesheet" type="text/css" href="css/buttons.css">         
    <link rel="stylesheet" type="text/css" href="css/normalize.css"/>
    <link rel="stylesheet" type="text/css" href="css/datepicker.css"/>    
    <link rel="stylesheet" type="text/css" href="css/jquery.jqplot.min.css" />
    <link rel="stylesheet" type="text/css" href="css/jquery.jqplot.css" />
        
    <script type="text/javascript" src="js/jquery.min.js"></script>
    <script type="text/javascript" src="js/jquery-1.7.1.min.js"></script>
    <script type="text/javascript" src="js/jquery-ui-1.8.18.custom.min.js"></script>
    <script type="text/javascript">

    $( function() {
        var dateFormat = "mm/dd/yy",
            from = $( "#from" ).datepicker({
                                            defaultDate: getDate( this ),
                                            maxDate: new Date('<?php echo substr($dagb_datum2, 6,4)."-".substr($dagb_datum2, 3,2)."-".substr($dagb_datum2,0,2); ?>'),
                                            numberOfMonths: 1,
                                            showButtonPanel: false,
                                            showOtherMonths: true,
                                            selectOtherMonths: false,
                                            changeMonth: true,
                                            changeYear: true,
                                            showOn: "button",
                                            buttonImage: "images/leeg.png",
                                            buttonImageOnly: true,
                                            buttonText: "Selecteer van datum",
                                            showWeek: true,
                                            firstDay: 1
            }),
            to = $( "#to" ).datepicker({
                                            defaultDate: getDate( this ),
                                            minDate: new Date('<?php echo substr($dagb_datum1, 6,4)."/".substr($dagb_datum1, 3,2)."/".substr($dagb_datum1,0,2); ?>'),
                                            numberOfMonths: 1,
                                            showButtonPanel: false,
                                            showOtherMonths: true,
                                            selectOtherMonths: false,
                                            changeMonth: true,
                                            changeYear: true,
                                            showOn: "button",
                                            buttonImage: "images/leeg.png",
                                            buttonImageOnly: true,
                                            buttonText: "Selecteer t/m datum",
                                            showWeek: true,
                                            firstDay: 1
            });
        
            function getDate( element ) {
                var date;
                try {
                    date = $.datepicker.parseDate( dateFormat, element.value );
                    } catch( error ) {
                    date = null;
                    }
            
            return date;
            }
            
    });
    
    </script>

    <script type="text/javascript" src="js/jquery.jqplot.min.js"></script>
    <script type="text/javascript" src="js/jquery.jqplot.js"></script>
    <script type="text/javascript" src="js/jqplot.dateAxisRenderer.js"></script>
    <script type="text/javascript" src="js/jqplot.logAxisRenderer.js"></script>
    <script type="text/javascript" src="js/jqplot.canvasTextRenderer.js"></script>
    <script type="text/javascript" src="js/jqplot.canvasAxisTickRenderer.js"></script>
    <script type="text/javascript" src="js/jqplot.canvasAxisLabelRenderer.js"></script>
    <script type="text/javascript" src="js/jqplot.highlighter.js"></script>

</head>
<body>
    <table class="empty">
        <tr>
            <td width="270" class="vak1">

                <form name="form_administratie" class="form_administratie" method='get' >
                    <table align="center">
                        <tr><td><div class="data_">De administratie van: </div><br><b><div class="data_titel"><?php echo $record_administratie['administraties_naam']; ?></div></b></div></td></TR>
                    </table>
                    <table>
                        <tr class="vak2"><td align="left"><div class="data_">Adres is: <br><td align="left"><b><div class="pf"><?php echo $record_administratie['administraties_straat'] . " " . $record_administratie['administraties_huisnr'] . "<br>" . $record_administratie['administraties_postcode'] . " " . $record_administratie['administraties_plaats']; ?></div></b></td></tr>
                        <tr class="vak2"><td align="left"><div class="data_">Telefoonnr.: <br><td align="left"><b><div class="pf"><?php echo $record_administratie['administraties_telefoonnr']; ?></div></b></td></tr>
                        <tr class="vak2"><td align="left"><div class="data_">K.v.K. nr.: <br><td align="left"><b><div class="pf"><?php echo $record_administratie['administraties_kvk']; ?></div></b></td></tr>
                        <tr class="vak2"><td align="left"><div class="data_">B.T.W. nr.: <br><td align="left"><b><div class="pf"><?php echo $record_administratie['administraties_btwnr']; ?></div></b></td></tr>
                        <tr class="vak2"><td align="left"><div class="data_">E-mail adres: <br></div></td><td align="left"><b><div class="pf"><?php echo $record_administratie['administraties_email']; ?></div></b></td></tr>
                    </table>
                    <table align="left">
                        <tr><td>
                        <?php echo $homeknop.$verwijderknop.$bewerkknop.$lijstknop.$toevoegknop; ?>
                        </td></tr>
                    </table>
                </form>
            </td>
            <td class="programmadeel">
                <form name="form_zoekperiode" method='post'>
                    <table>
                    <tr><td class="programmadeel_1" rowspan="2"><img src="images/inkomsten_uitgaven_groen.png" alt="Inkomsten/Uitgaven"></a>
                    </td><td class="programmadeel_1"><img src="images/dummie_kop.jpg" class="menu_ink_uit_kop" alt="" >
                    </td><td class="programmadeel_1"><img src="images/dummie_kop.jpg" class="menu_ink_uit_kop" alt="" >
                    </td><td class="programmadeel_1"><img src="images/dummie_kop.jpg" class="menu_ink_uit_kop" alt="" >
                    </td><td class="programmadeel_1"><img src="images/dummie_kop.jpg" class="menu_ink_uit_kop" alt="" >
                    </td><td class="programmadeel_1"><img src="images/dummie_kop.jpg" class="menu_ink_uit_kop" alt="" >
                    </td><td class="programmadeel_1"><img src="images/dummie_kop.jpg" class="menu_ink_uit_kop" alt="" >
                    </td><td class="programmadeel_1"><img src="images/dummie_kop.jpg" class="menu_ink_uit_kop" alt="" >
                    </td></tr>
                    <tr><td colspan="7" height="490" bgcolor=#F3BB7A valign="top">
                        <table align="center" width="745">
                            <tr><td align="center">
                                <h1><B><?php echo $melding; ?><B></h1>
                            </td><td>
                            <tr><td align="center" Valign="middle">
                                <div class="pf">Periode van: <label for="from"></label><?php echo $dagb_datum1; ?>
                                <input type="hidden" id="from" name="dagb_datum1" value=<?php echo $dagb_datum1; ?> class="button_datum" onchange="this.form.submit();" name="form_zoekperiode"> t/m <label for="to"></label><?php echo $dagb_datum2; ?>
                                <input type="hidden" id="to" name="dagb_datum2" value=<?php echo $dagb_datum2; ?> class="button_datum" onchange="this.form.submit();" name="form_zoekperiode">                            
                                </div>
                            </td></tr>
                            <tr><td align="center" Valign="middle"><HR></td></tr>
                        </table>
                        <table align="center" width="745">
                            <tr><td>
                                <table align="center" width="745">    
                                    <tr><td colspan="7" align="center">
                                        <button type="submit" src="images/leeg.png" class="button_delete" name="knop" alt="Verwijderen" disabled ></button>
                                        <button type="reset" src="images/leeg.png" class="button_reset" name="knop" alt="Standaard instellingen" disabled></button>
                                        <?php echo $linkdagboek_knop_lijst;?>
                                        <button type="submit" src="images/leeg.png" class="button_grafiek" name="knop" alt="Grafiek" disabled ></button>
                                        <button type="submit" src="images/leeg.png" class="button_add" name="knop" alt="Toevoegen" disabled ></button>
                                    </td></tr>
                                    <tr><td align="center">
                                    <?php // *** hier fouten tonen ***
                                    if(count($errors_programmafout)) {
                                        echo '<ul id="programmafout">';
                                        foreach($errors_programmafout as $error_programmafout) {
                                            echo '<li>' . $error_programmafout . '</li>';
                                        }
                                        echo '</ul>';
                                    }
                                    ?>
                                    </td></tr>
                                    <tr><td align="center">
                                    <div id="chart1" style="height:300px; width:745px;"></div>
                                    <script type="text/javascript">
                                        $(document).ready(function(){
                                            $.jqplot.config.enablePlugins = true;
                                            opts.cursor = {
                                                zoom:true,
                                                looseZoom: true,
                                                showTooltip:true,
                                                followMouse: true,
                                                showTooltipOutsideZoom: true,
                                                constrainOutsideZoom: false
                                            };
                                            var line1 = <?php echo json_encode($journaalposten); ?>;
                                            
                                            var plot1 = $.jqplot('chart1', [line1], {
                                            seriesColors: ["rgba(78, 135, 194, 0.7)", "rgb(211, 235, 59)"],
                                            highlighter: {
                                                show: true,
                                                sizeAdjust: 1,
                                                tooltipOffset: 9
                                            },
                                            grid: {
                                                background: 'rgba(57,57,57,0.0)',
                                                drawBorder: false,
                                                shadow: false,
                                                gridLineColor: '#666666',
                                                gridLineWidth: 1
                                            },
                                            legend: {
                                                show: false,
                                                placement: 'outside'
                                            },
                                            seriesDefaults: {
                                                fill: false,
                                                fillToZero: true,
                                                fillAndStroke: true,
                                                rendererOptions: {
                                                    smooth: false,
                                                    animation: {
                                                        show: false
                                                    }
                                                },
                                                showMarker: false
                                            },
                                            series: [
                                                {
                                                    label: 'debet-cerdit'
                                                }
                                            ],
                                            axesDefaults: {
                                                rendererOptions: {
                                                    baselineWidth: 1,
                                                    baselineColor: '#444444',
                                                    drawBaseline: false
                                                }
                                            },
                                            axes:{
                                                    xaxis: {
                                                        label: 'Datum',
                                                        labelRenderer: $.jqplot.CanvasAxisLabelRenderer,
                                                        labelOptions: {
                                                            fontFamily: 'Georgia, Serif',
                                                            fontSize: '12pt'
                                                        },
                                                        renderer: $.jqplot.DateAxisRenderer,
                                                        tickRenderer: $.jqplot.CanvasAxisTickRenderer,
                                                        tickOptions: {
                                                            formatString: "%e %b",
                                                            angle: -40,
                                                            textColor: '#000000'
                                                        },
                                                        min: "<?php echo $datum1; ?>",
                                                        max: "<?php echo $datum2; ?>",
                                                        tickInterval: "1 days",
                                                        drawMajorGridlines: false
                                                    },
                                                    yaxis: {
                                                        label: 'Totaal',
                                                        labelRenderer: $.jqplot.CanvasAxisLabelRenderer,
                                                        labelOptions: {
                                                            fontFamily: 'Georgia, Serif',
                                                            fontSize: '12pt'
                                                        },
                                                        renderer: $.jqplot.LogAxisRenderer,
                                                        rendererOptions: {
                                                            minorTicks: 1
                                                        },
                                                        tickRenderer: $.jqplot.CanvasAxisTickRenderer,
                                                        tickOptions: {
                                                            formatString: " %.2f",
                                                            showMark: false,
                                                            textColor: '#000000'
                                                        }                    
                                                    }
                                                },
                                                series:[{lineWidth:4, markerOptions:{style:'square'}}],
                                                cursor:{
                                                    show: true,
                                                    zoom:true,
                                                    showTooltip:false
                                                }
                                            });
                                            $('.jqplot-highlighter-tooltip').addClass('ui-corner-all')
                                            jsDate.regional['nl'] = {
                                                monthNames      : 'januari februari maart april mei juni juli augustus september oktober november december'.split(' '),
                                                monthNamesShort : 'jan feb mrt apr mei jun jul aug sep okt nov dec'.split(' '),
                                                dayNames        : 'zondag maandag dinsdag woensdag donderdag vrijdag zaterdag'.split(' '),
                                                dayNamesShort   : 'zo ma di wo do vr za'.split(' ')
                                            };
                                        });

                                    </script>
                                    </tr></td>
                                </table>
                            </td></tr>
                        </table>
                    </td></tr>
                    </table>
                </form>
            </td>
        </tr>
    </table>
</body>
</html>