Numerous addition and subtraction elements in one query

[Steve_NI]

Following the guidelines I have a the following query

Select Sum ((Select SUM(Balance) from account)-(Select sum((select sum(Balance) from credit_card) - (select sum(Amount) FROM bills where Date > 12)))

I want to tally up the total users have in their bank accounts(the database will only have one user) so if they have two accounts one with a balance of 1000 and the other with a balance of 500 their total is 1500. From this I want to deduct their credit card balance plus a total of any bills left to be paid before the end of the month. So if its the 12th today and they have a dd for electricity for 50 on the 15th and a dd for gas on the 25th for 100 thats 150, and their current credit card bill is 600, that means (600 +150)= 750. This is taken away from their account total (1500-750) thus our user has 750 to do him the rest of the month.

 

Here is the table structure

CREATE TABLE `bills` (
 `BillID` int(11) NOT NULL AUTO_INCREMENT,
 `Payee` varchar(100) NOT NULL,
 `Amount` double(6,2) NOT NULL,
 `Accname` varchar(50) NOT NULL,
 `Date` int(11) NOT NULL,
 PRIMARY KEY (`BillID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

CREATE TABLE `account` (
 `accID` int(11) NOT NULL AUTO_INCREMENT,
 `Bankname` varchar(50) NOT NULL,
 `Accname` varchar(50) NOT NULL,
 `Sortcode` int(11) NOT NULL,
 `Accnum` varchar( NOT NULL,
 `Balance` double NOT NULL,
 `PasswordHint` varchar(100) NOT NULL,
 `OverdraftLimit` double NOT NULL,
 PRIMARY KEY (`accID`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1

CREATE TABLE `credit_card` (
 `CreditCardID` int(11) NOT NULL AUTO_INCREMENT,
 `ProviderName` varchar(50) NOT NULL,
 `AccountNumber` int(11) NOT NULL,
 `PasswordHint` varchar(50) NOT NULL,
 `CreditLimit` int(11) NOT NULL,
 `InterestRate` double NOT NULL,
 `PaymentDay` int(11) NOT NULL,
 `Accname` varchar(50) NOT NULL,
 `Balance` int(11) NOT NULL,
 PRIMARY KEY (`CreditCardID`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

On running the query I get

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

But I have no quotes on line 1!

 

I have tried running the two queries individually. So I can total the balances and I can add the bills to the credit card bills its only when I try to put the two together does it fall down.

 

Apologies if this is too long I have tried to stick by the rules in the sticky!!!

[DavidAM]

the right syntax to use near '' at line 1

The quotes in that message are surrounding the part of the SQL that it had a problem with -- Line 1 refers to line 1 of the query (it's all on one line, so that is not helpful).

 

Your query appears to be missing a closing parenthesis at the end. So the quotes in the error are referring to the nothing it found when looking for that closing parenthesis.

 

Here is what your query looks like - I split it out so the groupings are more visible:

Select Sum (
               (Select SUM(Balance) from account) -
               (Select sum(
                            (select sum(Balance) from credit_card) - 
                            (select sum(Amount) FROM bills where Date > 12)
                          )
               )

[Steve_NI]

The quotes in that message are surrounding the part of the SQL that it had a problem with -- Line 1 refers to line 1 of the query (it's all on one line, so that is not helpful).

 

Your query appears to be missing a closing parenthesis at the end. So the quotes in the error are referring to the nothing it found when looking for that closing parenthesis.

 

Here is what your query looks like - I split it out so the groupings are more visible:

Select Sum (
               (Select SUM(Balance) from account) -
               (Select sum(
                            (select sum(Balance) from credit_card) - 
                            (select sum(Amount) FROM bills where Date > 12)
                          )
               )

Many thanks for coming back to me.

I have fixed the missing parentheses but when I run the query now I get

#1630 - FUNCTION finance_checker.Sum does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

In this case finance_checker is the name of the datavase containing all the tables. Any ideas how can the SUM function not work for a MySQL database?

[Barand]

Don't leave a space between the SUM and the (

 

ie SUM( and not SUM (

 

Another solution would be

SELECT SUM(total)
FROM
    (
    SELECT SUM(balance) as total FROM account
    UNION
    SELECT SUM(-balance) as total FROM credit_card
    UNION
    SELECT SUM(-amount) as total FROM bills WHERE date > 12
    ) as tots

[Steve_NI]

 

Don't leave a space between the SUM and the (

 

ie SUM( and not SUM (

 

Another solution would be

SELECT SUM(total)
FROM
    (
    SELECT SUM(balance) as total FROM account
    UNION
    SELECT SUM(-balance) as total FROM credit_card
    UNION
    SELECT SUM(-amount) as total FROM bills WHERE date > 12
    ) as tots

Thats its sorted now! Cheers Barand, who would have known a space would cause so much chaos (apart from you obviously lol!) Thanks for your help guys.

[Steve_NI]

Sorry, one final issue. I had to change around the code slightly to take into consideration that the balance on the credit card wil be a negative etc

So it looks like:

Select Sum(
               (Select SUM(Balance) from account) +
               (Select sum(
                            (select sum(Balance) from credit_card) +
                            (select sum(-Amount) FROM bills where Date > $today)
                          )
               )
    ) as Total

But if I have a balance on the bank accounts of 742, a balance on credit cards of -948 and bills still to come out before the end of the month at 189.88 the resultant query produces a total of -405.88000000000001.

How do I get it rounded down to just the two decimal places?

[Barand]

number_format in the PHP

 

or

 

use ROUND() in the query

 

SELECT ROUND(SUM(....), 2) as total

Edited November 13, 2013 by Barand

[Steve_NI]

number_format in the PHP

 

or

 

use ROUND() in the query

 

SELECT ROUND(SUM(....), 2) as total

Thats perfect Barand, once again many thanks for your help.