List:General Discussion« Previous MessageNext Message »
From:Robin Brady Date:November 9 2009 4:50pm
Subject:2 Queries need to combine into one
View as plain text  
I am very new to MySQL and trying to use Navicat Report Builder to format a renewal
invoice to send to our registrants.  The renewal fees are fixed  for each type of
registrant but the actual fee is not part of the database and must be computed as the
report is generated.  As far as I can tell, the Report Builder SUM function is fairly
basic and can only SUM actual fields in the database.  If I can format a query to compute
the sum and create a data view in the report builder I can put the total for each firm on
the report.

I have 2 separate queries that will compute the total renewal fees for branches and total
renewal fees for an agents but I can't figure out how to add these 2 numbers together in
the query.

Here are the 2 queries.  Note that there will always be at least 1 branch fee but there
may be >= 0 agent fees per firm.

SELECT dealer.FIRMID, (COUNT(*) -1) * 25.00 + 270.00 AS FEE
FROM `dealer` `dealer`
      INNER JOIN `branches` `branches` ON 
     (`branches`.`FIRMID` = `dealer`.`FIRMID`)
WHERE (( `dealer`.`CRD_NUM` = 0 )
       OR ( `dealer`.`CRD_NUM` IS NULL  ))
       AND ( `dealer`.`LIC_TYPE` IN (1,2,3,7,9) )
       AND ( `branches`.`BRANCH_NUM` >= 0 )
       AND ( `branches`.`STATUSID` = 31 )
GROUP BY `dealer`.`FIRMID`
ORDER BY `dealer`.`FILE_NUM`


SELECT dealer.FIRMID, (COUNT(*) * 275.00) AS FEE
FROM `dealer` `dealer`
      INNER JOIN `branches` `branches` ON 
     (`branches`.`FIRMID` = `dealer`.`FIRMID`)
      INNER JOIN `agentdealer` `agentdealer` ON 
     (`dealer`.`FIRMID` = `agentdealer`.`FIRMID`)
WHERE (( `dealer`.`CRD_NUM` = 0 )
       OR ( `dealer`.`CRD_NUM` IS NULL  ))
       AND ( `dealer`.`LIC_TYPE` IN (1,2,3,7,9) )
       AND (branches.BRANCH_NUM= 0)
       AND (branches.STATUSID = 31)
       AND ( `agentdealer`.`STATUSID` = 31 )
GROUP BY `dealer`.`FIRMID`
ORDER BY `dealer`.`FIRMID`


Thread
2 Queries need to combine into oneRobin Brady9 Nov
  • Re: 2 Queries need to combine into oneAnanda Kumar10 Nov
    • Re: 2 Queries need to combine into oneRobin Brady10 Nov
      • Re: 2 Queries need to combine into onePeter Brawley10 Nov
        • Re: 2 Queries need to combine into oneAnanda Kumar11 Nov