dimanche 27 août 2017

Laravel 5 SQL syntax multiple values from same table

I have this table

id |  price | vat | type | client |
---+--------+-----+------+--------+
 1 |     7     2      0        1
 2 |     3   null     0        2
 3 |     3     1      0        3
 4 |     2     2      1        1

the type column refers to add/subtract

 type 0 = +
 type 1 = -

What is the best way to get this with SQL in Laravel 5?

client  total  items
------+-------+-----+
  1       5      2
  2       3      1
  3       4      1

I have this which is working until a point

$summaries = \DB::table('tablename')
        ->select(
            'id',
            \DB::raw('sum(IFNULL(price,0) + IFNULL(vat,0)) as total')
        )
        ->where([
            ['type', '<>', '1']
        ])
        ->groupBy('client')
        ->get();

but i don't know how to make the type 1 records to substract from the total

Thanks in advance



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire