SQL Query with Calculation...

Trying to get my head around this, so appreciate any help.

I have a table that contains some columns
Value1 - Gross currency value
Value2 - Gross currency value
Value3 - Gross currency value
VATValue1 - The amount of the VAT tax contained in Value1
VATValue2 - The amount of the VAT tax contained in Value2
VATValue3 - The amount of the VAT tax contained in Value3

All could be negative or positive values.

What I need to do is is calculate the Net Value of the table, so summing all the Values and removing the VAT Tax amounts (which may be negative or positive)

It gets more complicated that I do an inner join to check which rows in the table need summing.

Currently my SQL is as below, which simply sums the Value1 and VATValue1 column, which I then repeat 3 times (or 1, 2 and 3) and then calculate the net value in code.

select Sum(Value1) as TaxSpend, Sum(VATValue1) as VATValue From Transactions 
   inner join Categories on Transactions.Category1 = Categories.ID
   where Categories.Tax=1 and Categories.Business = 1 and Transactions.TransactionDate Between '2014-04-01' and '2015-03-31'

What I am trying to work out is how to do this in one SQL statement (if it is possible).

Thanks

Phil

I have a feeling these tables aren’t properly normalised… this business of Category1 VAt1, etc…

Still, heres some options:

select Sum(Value1 ) as TaxSpend, Sum(VATValue1) as VATValue , sum(value1 + VATValue1) as NetValue ,Category1 From Transactions inner join Categories on Transactions.Category1 = Categories.ID where Categories.Tax=1 and Categories.Business = 1 and Transactions.TransactionDate Between '2014-04-01' and '2015-03-31' union select Sum(Value2 ) as TaxSpend, Sum(VATValue2) as VATValue , sum(value2 + VATValue2) as NetValue ,Category2 From Transactions inner join Categories on Transactions.Category2 = Categories.ID where Categories.Tax=2 and Categories.Business = 2 and Transactions.TransactionDate Between '2014-04-01' and '2015-03-31'

The purpose and naming of some of your fields aren’t immediately obvious from the example you supplied.

I am sure these tables aren’t properly normalised - as I am not that much up to speed with databases :slight_smile:

The Value1, 2, etc. is due to a record being able to have three spend lines, each with VAT Tax associated. Having three sets of columns for this seemed to me the cleanest way of doing this, but I am sure it there are better ways.

Thanks for the pointer re. the SQL, off to try it.

Phil