Member Avatar for adambeaumont

Good Evening,

I currently have an issue regarding a query I wish to perform against a particular table in my database, any help would be much apprieciated

Background:

I am designing a website service which operates with the use of credits, if they pay a fee they are given a number of credits, when they use the service these credits decrease..
i have implemented a transaction table which aims to store all the transactions of credits purchased and used.
ie. if a user purchases 2 credits the table transaction will be updated and the value of 1 will be inserted.

if a user uses their credits the table transaction will be updated and the value of 0 will be inserted, this allows for differentiation i.e. entries with a value of 0 mean a user has used their credits rather than purchase..


Table : Transactions

TransactionID , Credits , BuyerID, Value

1_____________2_________1______________1
2____________ 6________ 2_____________ 3
3____________ 4_________1______________2
4____________ 6_________1______________3
5____________2_________2_______________0


The BuyerID field is the particular ID of the user
Credits - is the number of credits being used in the transaction
Value - value of the transaction


Aim: implement a query which selects the number of credits a user has remaining i.e. credits purchased - credits used (value 0)

Example- with the above table - the user with the BuyerID 2 table would have purchased 6 credits in TransactionID 2 and used 2 credits in TransactionID - remaining credits = 4

I hope I havn't confused anyone


Thanks

Adam

  • 2 Contributors
  • 1 Reply
  • 78 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by campkev
Member Avatar for campkev

I don't use mysql but both of these work in ms-sql:
query 1:

declare @buyerid int
set @buyerid = 2
declare @creds int
declare @debs int

set @creds =(select sum(credits) from Transactions where buyerid = @buyerid and value >0)
set @debs =(select sum(credits) from Transactions where buyerid = @buyerid and value =0)
select (@creds - @debs) as Balance

query 2:

declare @buyerid int
set @buyerid = 2
select (Sum(t1.credits) - Sum(t2.credits)) as Balance
from transactions t1, transactions t2 where
t2.buyerid = @buyerid and t1.buyerid = @buyerid and t1.value > 0 and t2.value = 0
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.