List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:November 10 2009 6:52pm
Subject:Re: 2 Queries need to combine into one
View as plain text  
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 <anandkl@stripped> 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 <rbrady@stripped> 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=1 
>>
>>
>>     
>
>
>   
> ------------------------------------------------------------------------
>
>
> 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
>
>   

Thread
2 Queries need to combine into oneRobin Brady9 Nov
  • Re: 2 Queries need to combine into oneAnanda Kumar10 Nov
    • Re: 2 Queries need to combine into oneRobin Brady10 Nov
      • Re: 2 Queries need to combine into onePeter Brawley10 Nov
        • Re: 2 Queries need to combine into oneAnanda Kumar11 Nov