Sum subquerys values separately

Asked

Viewed 79 times

2

I have a problem in this query below:

select
  sum(tb1.l1) * 0.3,
  sum(tb2.l2) * 0.3
from
  (
    select
      setor,
      total_geral as l1
    from
      mobile.auditoria
    where
      month(data) = month(now())
      and year(data) = year(now())
      and setor regexp '[1]' = 1
    group by
      setor
    order by
      data
  ) as tb1,
  (
    select
      setor,
      total_geral as l2
    from
      mobile.auditoria
    where
      month(data) = month(now())
      and year(data) = year(now())
      and setor regexp '[2]' = 1
    group by
      setor
    order by
      data
  ) as tb2;

when I do so:

select
  sum(tb1.l1) * 0.3,
  sum(tb2.l2) * 0.3
from
  (
    select
      setor,
      total_geral as l1
    from
      mobile.auditoria
    where
      month(data) = month(now())
      and year(data) = year(now())
      and setor regexp '[1]' = 1
    group by
      setor
    order by
      data
  ) as tb1;

brings me the right sum of totals times 0.3, but when adding the second subquery, tb2, the value is accumulated.

Does anyone know why you are giving this problem? I would like to bring the values without accumulating, as it is done when I do in a single subquery.

mysql sql query

1 answer

1

Try putting the SUM within each of the SUBSELECT instead of using them in the main query:

select  tb1.l1 * 0.3
    ,   tb2.l2 * 0.3  
from    (
            select      setor
                    ,   sum(total_geral) as l1  
            from        mobile.auditoria 
            where       month(data)         = month(now())  
                    and year(data)          = year(now()) 
                    and setor regexp '[1]'  = 1 
            group by    setor 
        ) as tb1,
        (
            select      setor
                    ,   sum(total_geral) as l2  
            from        mobile.auditoria 
            where       month(data)         = month(now()) 
                    and year(data)          = year(now()) 
                    and setor regexp '[2]'  = 1 
            group by    setor 
        ) as tb2

Browser other questions tagged mysql sql query

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