select * from a; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec)
mysql> select * from b; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) mysql> mysql> select (select id from a where id=1) + (select id from b where id=1); +---------------------------------------------------------------+ | (select id from a where id=1) + (select id from b where id=1) | +---------------------------------------------------------------+ | 2 | +---------------------------------------------------------------+ It works On Wed, Nov 11, 2009 at 12:22 AM, Peter Brawley <peter.braw...@earthlink.net > wrote: > Robin, > > > << > 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` > >> >>> > We can't "add" two resultsets together using a plus sign. For that, we need > the UNION operator. But there seems to be a simpler solution: is this what > you mean? > > SELECT d.firmID, IF(b.branch_num=0 AND a.statusID=31, COUNT(*) * 275, > (COUNT(*)-1) * 25 + 270 ) AS Fee > FROM dealer d > JOIN branches b ON b.firmID = d.firmID > LEFT JOIN agentdealer a ON d.firmID = a.firmID > WHERE ( d.crd_num = 0 OR d.crd_num IS NULL ) > AND d.lic_type IN (1,2,3,7,9) > AND b.statusID = 31 > GROUP BY d.firmID; > > PB > http://www.artfulsoftware.com > > ----- > > > Robin Brady wrote: > >> 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 >>> >>> >>> >> >> >> ------------------------------------------------------------------------ >> >> >> No virus found in this incoming message. >> Checked by AVG - www.avg.com Version: 8.5.425 / Virus Database: >> 270.14.59/2494 - Release Date: 11/10/09 07:38:00 >> >> >> >
