mysql sum coulmn which as query result

August 23, 2023

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

>Solution :

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