dimanche 3 janvier 2016

SQLite usin Sum()-function in a VIEW

so i created a view, in this view i have person_id's, account_id's, dates, payment methods and value. in the payment method i specified two different ones: withdrawal and deposit. withdrawal is shown as a negative value. what i want to do now is to print out all account_id's where the sum of withdrawals and deposits is negative.

this is my CREATE VIEW code:

CREATE VIEW payment
    ( personID, AacountID, date, payment_method, value )
AS SELECT pers_id, acc_id, date, 'deposit', value
    FROM deposit
UNION ALL
SELECT pers_id, acc_id, date, 'withdrawal', -value
FROM withdrawal

I can't use the sum()-function inside the create view statement, i have to use the view to print out all accounts with an overall negative value. I've been searching for a while now, but i just found how to use the function inside the create view statement.

Aucun commentaire:

Enregistrer un commentaire