Add subquery values separately

Question

Add subquery values separately

Navigation

#1 by (1 votes)

2

I have a problem with 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 this:

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;

It brings me the right value of the sum of the total times 0.3, but when I add the second subquery, tb2 , the value comes accumulated.

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

mysql sql query

asked by anonymous 18.10.2018 / 16:37

1 answer

Close modal when clicking the button What is a singleton variable in prolog?

score 1 · Answer 1

Try to put SUM within each 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