0

I have this SQL code

create schema if not exists test; use test;

drop table if exists contas;
create table if not exists contas (
    id_conta integer not null AUTO_INCREMENT,
    tipo enum('D', 'C') not null,
    valor decimal(10,2) not null,
    data timestamp not null default current_timestamp,
    primary key(id_conta));

insert into contas(tipo,valor,data) 
    values 
        ('C', 1000, date_add(NOW(), interval 20 minute)),
        ('C', 340, date_add(NOW(), interval 23 minute));

insert into contas(tipo,valor,data) 
    values
        ('D', 199, date_add(NOW(), interval 1 minute)),
        ('D', 200, date_add(NOW(), interval 2 minute)),
        ('D', 14, date_add(NOW(), interval 3 minute));


(select data.* from (select
        id_conta,
        tipo, 
        date(data) as vencimento,
        sum(valor) as valor
    from 
        contas
    group by tipo, data 
    with rollup
) as data
-- -- where (data.tipo and data.vencimento) is not null
order by data.tipo, data.vencimento desc);

insert into contas(tipo,valor,data) values('D', 234.49, date_add(NOW(), interval 1 minute));
insert into contas(tipo,valor,data) values('D', 334.00, date_add(NOW(), interval 2 minute));

insert into contas(tipo,valor,data) values('C', 49.50, date_add(NOW(), interval 1 minute));
insert into contas(tipo,valor,data) values('C', 42.00, date_add(NOW(), interval 2 minute));
insert into contas(tipo,valor,data) values('C', 100.00, date_add(NOW(), interval 3 minute));

insert into contas(tipo,valor,data) values('C', 10.00, '2013-04-30 23:59:59');
insert into contas(tipo,valor,data) values('C', 20.00, '2013-04-30 23:59:59');
insert into contas(tipo,valor,data) values('C', 50.00, '2013-04-30 23:59:59');

insert into contas(tipo,valor,data) values('D', 10.00, '2013-05-02 23:59:59');
insert into contas(tipo,valor,data) values('D', 20.00, '2013-05-02 23:59:59');
insert into contas(tipo,valor,data) values('D', 60.00, '2013-05-02 23:59:59');

This will produce some data, and i execute SELECT and get the following output:

+----------+------+------------+---------+
| id_conta | tipo | vencimento | valor   |
+----------+------+------------+---------+
|       14 | NULL | NULL       | 3677.47 | --> This guy
|       14 | C    | 2013-04-30 |   80.00 |
|       22 | C    | 2013-04-17 |  100.00 |
|        2 | C    | 2013-04-17 |  340.00 |
|        9 | C    | 2013-04-17 |   49.50 |
|       10 | C    | 2013-04-17 |   42.00 |
|       11 | C    | 2013-04-17 |  100.00 |
|        1 | C    | 2013-04-17 | 1000.00 |
|       20 | C    | 2013-04-17 |   49.50 |
|       21 | C    | 2013-04-17 |   42.00 |
|       14 | C    | NULL       | 1803.00 |
|       15 | D    | 2013-05-02 |   90.00 |
|        3 | D    | 2013-04-17 |  199.00 |
|        4 | D    | 2013-04-17 |  200.00 |
|        5 | D    | 2013-04-17 |   14.00 |
|        6 | D    | 2013-04-17 |  234.49 |
|        7 | D    | 2013-04-17 |  234.49 |
|        8 | D    | 2013-04-17 |  334.00 |
|       18 | D    | 2013-04-17 |  234.49 |
|       19 | D    | 2013-04-17 |  334.00 |
|       15 | D    | NULL       | 1874.47 |
+----------+------+------------+---------+
21 rows in set (0.00 sec)

The first row is the sum of all, but, is there an way to make it's value as sum of all C and subtracted by D (C = Credit, D = Debit)

In SQL ? pure SQL I've been searched, and did not found anything.

Haro Ken
  • 41
  • 1
  • 5
  • 2
    Do two sums and subtract them? On the other hand, it's much more efficient to store credit as negative value and debit as positive and then just do a SUM across single column. – N.B. Apr 17 '13 at 08:53
  • `"The first row is the sum of all"` - that's not good database design. Or is that not an actual row, just included to show it? – Bernhard Barker Apr 17 '13 at 08:58
  • It will not be possible using query... As `with rollup` shows super-aggregate operations on the group. As you are using `sum()` and then you cannot change its behavior to deduct the values. You can handle this in the application side or you can create a new query for this or follow other user suggestion of storing debit value as negative... – Meherzad Apr 17 '13 at 10:21

0 Answers0