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
