CODEWITHSUNDEEP
sql
user2062455
user2062455

Reputation: 421

better version of this sql?

I am trying to create some counts in my query and my code recalculates the sums over and over. is there a better way of writting this?

if(
    ( 
       (
         select count(`t2`.`id`) 
         from `qbinvoices` `t2` 
         where  `t2`.`qbAccNumber` = `t1`.`qbAccNumber`
            and `t2`.`divisionId` = `t1`.`divisionId`
        ) > 0
    ),
    (
      select count(`t2`.`id`) 
      from `qbinvoices_view` `t2`
      where  `t2`.`qbAccNumber` = `t1`.`qbAccNumber`
         and `t2`.`monthStart` > 0
         and `t2`.`divisionId` = `t1`.`divisionId`
    ),
    (
      select count(`t2`.`id`) 
      from `qbestimatesnew_view` `t2` 
      where `t2`.`qbAccNumber` = `t1`.`qbAccNumber` 
          and `t2`.`divisionId` = `t1`.`divisionId`
    )
) AS `invoiceItemsCount`

I am using this:

select count(`t2`.`id`) 
from `qbinvoices` `t2` 
where  `t2`.`qbAccNumber` = `t1`.`qbAccNumber`
    and `t2`.`divisionId` = `t1`.`divisionId`

inside an If statement. ie: if(mystatement > 0, mystatement, differentVersionOfMyStatement)

I am also using this inside a view, so I cannot use variables. Is there any way of writing it without repeating the count three times inside the If statement?

Upvotes: 1

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I would express this logic as:

(case when exists (select 1
                   from `qbinvoices` `t2` 
                   where  `t2`.`qbAccNumber` = `t1`.`qbAccNumber` and
                          `t2`.`divisionId` = `t1`.`divisionId`
                  ) 
     then (select count(`t2`.`id`) 
           from `qbinvoices_view` `t2`
           where  `t2`.`qbAccNumber` = `t1`.`qbAccNumber` and
                  `t2`.`monthStart` > 0 and
                  `t2`.`divisionId` = `t1`.`divisionId`
         )
     else (select count(`t2`.`id`) 
           from `qbestimatesnew_view` `t2` 
           where `t2`.`qbAccNumber` = `t1`.`qbAccNumber` and
                 `t2`.`divisionId` = `t1`.`divisionId`
          )
end) AS `invoiceItemsCount`

count() is an expensive operation for determining if records exist. exists has the advantage of stopping at the first matching records.

The other two counts are from different tables.

Also, I prefer case to data-specific syntax such as if().

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415921

No, because the code does not repeat the same count. Each of the existing counts looks at a different data source (qbinvoices vs qbinvoices_view vs qbestimatesnew_view) with a different possible result, and therefore all must be used in the sql code.

What you might be able to do instead is use JOINs in combination with conditional aggregation to roll them up in a way that is simpler to express in the SELECT clause and possibly has a faster execution plan. But we'd need to see the entire SQL query to show you more what that looks like here.

Upvotes: 1

Related Questions