Jump to content


Photo

Numerous addition and subtraction elements in one query


Best Answer Barand, 13 November 2013 - 04:44 AM

number_format in the PHP

 

or

 

use ROUND() in the query

 

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

Go to the full post


  • Please log in to reply
7 replies to this topic

#1 Steve_NI

Steve_NI

    Member

  • Members
  • PipPip
  • 25 posts

Posted 12 November 2013 - 05:11 PM

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(8) 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!!!



#2 DavidAM

DavidAM

    Advanced Member

  • Gurus
  • 1,974 posts
  • LocationSpring, TX USA

Posted 12 November 2013 - 06:00 PM

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)
                          )
               )

-- I haven't lost my mind, it's backed up on tape ... somewhere!

#3 Steve_NI

Steve_NI

    Member

  • Members
  • PipPip
  • 25 posts

Posted 12 November 2013 - 06:19 PM

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?



#4 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,365 posts
  • LocationCheshire, UK

Posted 12 November 2013 - 06:27 PM

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

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#5 Steve_NI

Steve_NI

    Member

  • Members
  • PipPip
  • 25 posts

Posted 13 November 2013 - 03:10 AM

 

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.



#6 Steve_NI

Steve_NI

    Member

  • Members
  • PipPip
  • 25 posts

Posted 13 November 2013 - 04:43 AM

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?



#7 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,365 posts
  • LocationCheshire, UK

Posted 13 November 2013 - 04:44 AM   Best Answer

number_format in the PHP

 

or

 

use ROUND() in the query

 

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


Edited by Barand, 13 November 2013 - 04:46 AM.

moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#8 Steve_NI

Steve_NI

    Member

  • Members
  • PipPip
  • 25 posts

Posted 13 November 2013 - 05:12 AM

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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com