Add values to total - SQL

Asked

Viewed 89 times

3

I have the following query

SELECT 
    sum(tbl_purchase_product.sub_total) as sub_total 
FROM 
    (`tbl_purchase_product`) 
WHERE 
    `tbl_purchase_product`.`purchase_id` = '3'

I have a field called others_price, and I need to add it to sub_total, as I could add by query?

sql

  • sum(tbl_purchase_product.sub_total) + sum(tbl_purchase_product.others_price) as sub_total?

  • I’m not sure that works: sum(tbl_purchase_product.sub_total + tbl_purchase_product.others_price) as sub_total but I believe so

  • 1

    You need to add to each line, or you need the total amount?

  • I just wanted to add + others_price, I don’t want to add everything you have in the field...

  • 1

    @Mr Andrébaill, if any of the answers are correct, mark it as such! Why it is important to vote?. Also give an UP to the answers that have been useful to you.

3 answers

6

You can do it this way:

SELECT 
    tbl_purchase_product.sub_total + tbl_purchase_product.others_price as sub_total 
FROM 
    (`tbl_purchase_product`) 
WHERE 
    `tbl_purchase_product`.`purchase_id` = '3'

As you are searching for the id I believe you need to add only this record, but if you want to add of all, you can do as follows:

SELECT 
    sum(tbl_purchase_product.sub_total + tbl_purchase_product.others_price) as sub_total 
FROM 
    (`tbl_purchase_product`) 
WHERE 
    `tbl_purchase_product`.`purchase_id` = '3'

Some example to indicate what your problem is

Imagine the following example:

ID  VALUE1  VALUE2
===================
1   1       2
1   2       2
2   3       4
2   4       5

If you do the following query:

SELECT  ID, SUM(VALUE1), SUM(VALUE2)
FROM    tableName
GROUP   BY ID

The result will be:

ID, SUM(VALUE1), SUM(VALUE2)
1   3           4
2   7           9

While if you do it this way:

SELECT  ID, VALUE1 + VALUE2
FROM    TableName

The result will be:

ID, VALUE1 + VALUE2
1   3
1   4
2   7
2   9

And on the other hand doing it this way:

SELECT  ID, SUM(VALUE1 + VALUE2)
FROM    tableName
GROUP   BY ID

The result:

ID, SUM(VALUE1 + VALUE2)
1   7
2   16

Source

Update

Reply after clarification in the comments

SELECT 
    sum(tbl_purchase_product.sub_total) + tbl_purchase_product.others_price as sub_total 
FROM 
    (`tbl_purchase_product`) 
WHERE 
    `tbl_purchase_product`.`purchase_id` = '3'

  • In this case, it might work... but he’ll add up everyone who has the same field, I need to add up only once the field..

  • sum(tbl_purchase_product.sub_total) + tbl_purchase_product.others_price as sub_total

  • The first example, it would sum each record separately, showing the sum of each record separately, while in the second it would sum each record and sum it all into one set, which you need?

  • I need to add once, I don’t want to add up all the records, I used the way I gave it to you now and it worked for what I need :)

  • I edited with the excerpt you put in the comments

6


Do so :

SELECT SUM(( sub_total ) + ( others_price )) 
FROM   (SELECT Count(tbl_purchase_product.sub_total) SUB_TOTAL, 
               Count(others_price)                   OTHERS_PRICE 
        FROM   tbl_purchase_product 
        WHERE  tbl_purchase_product `.` purchase_id ` = '3')

1

From what I understand you want to add the others_price after the sum, so try it this way:

SELECT      TPP2.sub_total + TPP.others_price
FROM        tbl_purchase_product    TPP
INNER JOIN  (
                SELECT      SUM(sub_total) AS sub_total 
                FROM        tbl_purchase_product 
                GROUP BY    purchase_id
            )                       TPP2 ON TPP2.purchase_id = TPP.purchase_id
WHERE       TPP.purchase_id = '3'

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.