CODEWITHSUNDEEP
sql
lelot
lelot

Reputation: 596

AVG of SUM in SQL

Here's my code:

SELECT B1_COD, SB1030.B1_DESC, B2_CM1, B2_QATU, SUM (B2_QATU)
FROM SB1030 
INNER JOIN SB2030 ON B1_COD = B2_COD
WHERE (B1_TIPO='PA') AND (B2_QATU <> '0')

I'd like to:

Could anyone help me?????

Thanks

Upvotes: 0

Views: 130

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Here is what I think you are looking for. A list of b1_cods with sums and a calculation based on the sums.

select 
  b1_cod, 
  sb1030.b1_desc, 
  sum(b2_cm1) as sum_cm1, 
  sum(b2_qatu) as b2_sal, 
  sum(b2_qatu * b2_cm1) as b2_val,
  sum(b2_qatu) / sum(b2_qatu * b2_cm1) as new_field
from sb1030 
inner join sb2030 on b1_cod = b2_cod
where b1_tipo = 'pa' and b2_qatu <> '0'
group by b1_cod, sb1030.b1_desc
order by b1_cod, sb1030.b1_desc;

Upvotes: 1

Noobling
Noobling

Reputation: 23

Do you only want the result b2_VAL and b2_SAL?

Try using WITH CTE:

 WITH CTE AS(
 SELECT SUM(B1_COD) AS B1_COD, SB1030.B1_DESC,B2_CM1*B2_QATU AS b2_VAL, SUM (B2_QATU) AS  B2_QATU
 FROM SB1030 
 INNER JOIN SB2030 ON B1_COD = B2_COD
 WHERE (B1_TIPO='PA') AND (B2_QATU <> '0'))
 SELECT B1_COD+B2_QATU, b2_VAL
 FROM CTE

Is it something like that you are looking for?

Upvotes: 0

Related Questions