2 Queries need to combine into one

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 >=3D 0 agent fees per firm.

SELECT dealer.FIRMID, (COUNT(*) -1) * 25.00 + 270.00 AS FEE
FROM `dealer` `dealer`
INNER JOIN `branches` `branches` ON=20
(`branches`.`FIRMID` =3D `dealer`.`FIRMID`)
WHERE (( `dealer`.`CRD_NUM` =3D 0 )
OR ( `dealer`.`CRD_NUM` IS NULL ))
AND ( `dealer`.`LIC_TYPE` IN (1,2,3,7,9) )
AND ( `branches`.`BRANCH_NUM` >=3D 0 )
AND ( `branches`.`STATUSID` =3D 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=20
(`branches`.`FIRMID` =3D `dealer`.`FIRMID`)
INNER JOIN `agentdealer` `agentdealer` ON=20
(`dealer`.`FIRMID` =3D `agentdealer`.`FIRMID`)
WHERE (( `dealer`.`CRD_NUM` =3D 0 )
OR ( `dealer`.`CRD_NUM` IS NULL ))
AND ( `dealer`.`LIC_TYPE` IN (1,2,3,7,9) )
AND (branches.BRANCH_NUM=3D 0)
AND (branches.STATUSID =3D 31)
AND ( `agentdealer`.`STATUSID` =3D 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 2 Queries need to combine into one

--0016e64cc37a312ec30478014a94
Content-Type: text/plain; charset=ISO-8859-1

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 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=anandkl@gmail.com
>
>

--0016e64cc37a312ec30478014a94--

Re: 2 Queries need to combine into one

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 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` =3D `dealer`.`FIRMID`)
WHERE (( `dealer`.`CRD_NUM` =3D 0 )
OR ( `dealer`.`CRD_NUM` IS NULL ))
AND ( `dealer`.`LIC_TYPE` IN (1,2,3,7,9) )
AND ( `branches`.`BRANCH_NUM` >=3D 0 )
AND ( `branches`.`STATUSID` =3D 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` =3D `dealer`.`FIRMID`)
INNER JOIN `agentdealer` `agentdealer` ON
(`dealer`.`FIRMID` =3D `agentdealer`.`FIRMID`)
WHERE (( `dealer`.`CRD_NUM` =3D 0 )
OR ( `dealer`.`CRD_NUM` IS NULL ))
AND ( `dealer`.`LIC_TYPE` IN (1,2,3,7,9) )
AND (branches.BRANCH_NUM=3D 0)
AND (branches.STATUSID =3D 31)
AND ( `agentdealer`.`STATUSID` =3D 31 )
GROUP BY `dealer`.`FIRMID`
ORDER BY `dealer`.`FIRMID`
)
On Mon, Nov 9, 2009 at 10:20 PM, Robin Brady =
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 >=3D 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` =3D `dealer`.`FIRMID`)
> WHERE (( `dealer`.`CRD_NUM` =3D 0 )
> OR ( `dealer`.`CRD_NUM` IS NULL ))
> AND ( `dealer`.`LIC_TYPE` IN (1,2,3,7,9) )
> AND ( `branches`.`BRANCH_NUM` >=3D 0 )
> AND ( `branches`.`STATUSID` =3D 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` =3D `dealer`.`FIRMID`)
> INNER JOIN `agentdealer` `agentdealer` ON
> (`dealer`.`FIRMID` =3D `agentdealer`.`FIRMID`)
> WHERE (( `dealer`.`CRD_NUM` =3D 0 )
> OR ( `dealer`.`CRD_NUM` IS NULL ))
> AND ( `dealer`.`LIC_TYPE` IN (1,2,3,7,9) )
> AND (branches.BRANCH_NUM=3D 0)
> AND (branches.STATUSID =3D 31)
> AND ( `agentdealer`.`STATUSID` =3D 31 )
> GROUP BY `dealer`.`FIRMID`
> ORDER BY `dealer`.`FIRMID`
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql=20
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Danandkl@gmail.com=
=20
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 2 Queries need to combine into one

--------------000601060200080909020906
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 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 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=anandkl@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
>
>

--------------000601060200080909020906--

Re: 2 Queries need to combine into one

--0016e649977a6d939a047812c8fa
Content-Type: text/plain; charset=ISO-8859-1

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 > 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 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
>> 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=anandkl@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
>>
>>
>>
>

--0016e649977a6d939a047812c8fa--