Initially I received "the operand should only have one column" so I removed the
dealer.FIRMID from the select statement and then the query just returns NULL.
Each query works fine on its own but I can't seem to combine it so that is
gives me the total of the fees.
Robin
>>> Ananda Kumar <anan...@gmail.com> 11/10/2009 3:54 AM >>>
select (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`
)
On Mon, Nov 9, 2009 at 10:20 PM, Robin Brady <rbr...@ssb.state.tx.us> wrote:
> 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=anan...@gmail.com
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
