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`



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org

Reply via email to