Question 235964 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql sum coulmn which as query result

i am using this query to get cost of the product

select 
  SUM(strg_op.opcost)/ SUM(strg_op.opvalue * 1000) * strg_catgs.Disrate as totalcost 
from 
  strg_catgs 
  INNER JOIN strg_op on strg_op.ElemID = strg_catgs.elemID 
  and strg_catgs.elemID =(
    select 
      strg_catgs.elemID 
    where 
      strg_catgs.catID = '13'
  ) 
  and strg_catgs.catID = '13' 
  and strg_catgs.descr = "cwe" 
GROUP by 
  strg_catgs.elemID

i get result like this

| totalcost| 
| -------- | 
| 1.1      | 
| 5.814681 |

i need result to sum all values in total cost column like (1.1 + 5.814681) but in one field like this

| totalcost |
| --------  |
| 6.9       |

when i trying to use this query

select 
  sum(
    SUM(strg_op.opcost)/ SUM(strg_op.opvalue * 1000) * strg_catgs.Disrate
  ) as totalcost 
from 
  strg_catgs 
  INNER JOIN strg_op on strg_op.ElemID = strg_catgs.elemID 
  and strg_catgs.elemID =(
    select 
      strg_catgs.elemID 
    where 
      strg_catgs.catID = '13'
  ) 
  and strg_catgs.catID = '13' 
  and strg_catgs.descr = "cwe" 
GROUP by 
  strg_catgs.elemID

not working

Tags:

2

Answers


  1. 0

    Since we can not see the sample data and table structure,so the solution below might not be working for you,but you can take it as a reference,there are two options can do it:

    a. remove group by(this seems a more concise choice)

    select 
  SUM(strg_op.opcost)/ SUM(strg_op.opvalue * 1000) * strg_catgs.Disrate as totalcost 
from 
  strg_catgs 
  INNER JOIN strg_op on strg_op.ElemID = strg_catgs.elemID 
  and strg_catgs.elemID =(
    select 
      strg_catgs.elemID 
    where 
      strg_catgs.catID = '13'
  ) 
  and strg_catgs.catID = '13' 
  and strg_catgs.descr = "cwe"

    b. add an outer query to sum the value:

    SELECT SUM(t.totalcost) AS totalcost
FROM
(
select 
  SUM(strg_op.opcost)/ SUM(strg_op.opvalue * 1000) * strg_catgs.Disrate as totalcost 
from 
  strg_catgs 
  INNER JOIN strg_op on strg_op.ElemID = strg_catgs.elemID 
  and strg_catgs.elemID =(
    select 
      strg_catgs.elemID 
    where 
      strg_catgs.catID = '13'
  ) 
  and strg_catgs.catID = '13' 
  and strg_catgs.descr = "cwe" 
GROUP by 
  strg_catgs.elemID
) AS t
    Login or Signup to reply.
  2. 0

    seems like you’re trying to calculate the total cost for each strg_catgs.elemID and then sum up those individual total costs to get a grand total. However, the approach you’ve taken in your second query might not yield the desired result because it’s trying to use the SUM function twice, which can cause incorrect aggregation.

    Here’s an alternative way to structure your query to achieve the desired result:

     SELECT SUM(totalcost) as grand_totalcost
 FROM (
 SELECT SUM(strg_op.opcost) / SUM(strg_op.opvalue * 1000) * 
 strg_catgs.Disrate as totalcost
 FROM strg_catgs
 INNER JOIN strg_op ON strg_op.ElemID = strg_catgs.elemID
 WHERE strg_catgs.elemID = 
 (SELECT 
 strg_catgs.elemID FROM strg_catgs WHERE 
 strg_catgs.catID = '13')
 AND strg_catgs.catID = '13' 
 AND strg_catgs.descr = "cwe"
 GROUP BY strg_catgs.elemID
 ) subquery;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search