how to select record from Db having Count() greater than 0

Question

User670644709 posted

Hi 

i have the following stored procedure that returns the sent emails of the latest or last inserted campaigns

"Now my aim to only show the campaigns which have "sent: count greater than "0" how could i make changes to the following sp to do it, because now it is returning all the campaigns including "0" and greater than "0" "..

Code is...

DELIMITER $$

DROP PROCEDURE IF EXISTS `couponcrusaderdev`.`sp_tblemailcampaignLoadTop4`$$

CREATE DEFINER=`anyone`@`000.000.00.00` PROCEDURE `sp_tblemailcampaignLoadTop4`(p_UserID Int(11))
    SQL SECURITY INVOKER
BEGIN
SELECT EC.*,(Select MailChimpCampaignID from tblcampaignschedule where EC.CampaignID=tblcampaignschedule.CampaignID ) as MailChimpCampaignID,(Select  count(MailChimpCampaignID) from tblcampaignschedule) as msent,CT.CampaignType, (Select tblcampaignschedule.ScheduleDateTime from tblcampaignschedule 
where EC.CampaignID=tblcampaignschedule.CampaignID  order By ScheduleDateTime desc limit 1) as CampaignDateTime,
(Select Count(*) from tblemailsent ES
inner join tblcampaignschedule CS on CS.CampaignSceduleID= ES.CampaignSceduleID
where ES.Status= 'ProcessedMessage' and CS.CampaignID = EC.CampaignID)as Sent
FROM tblemailcampaign EC
inner join tblcampalgntype  CT on CT.CampaignTypeID= EC.CampaignTypeID
inner join tblcompanies C On EC.CompanyID = C.CompanyID
WHERE (p_UserID = 0 OR C.AddUserID = p_UserID)
 order by CampaignDateTime desc limit 4
;
END$$

DELIMI

Answer 1

User670644709 posted

Some one has told me to use::

HAVING Count(*) > 0 before 

order by CampaignDateTime desc limit 4

BUt it returns an abnormal result ANY SUGGESTIONS???

Answer 2

User670644709 posted

The solution is to use 

HAVING Count(*) > 0  

with

Group by ------

And the working code is as follows:

DELIMITER $$

DROP PROCEDURE IF EXISTS `couponcrusaderdev`.`sp_tblemailcampaignLoadTop4`$$

CREATE DEFINER=`akhan`@`202.166.167.27` PROCEDURE `sp_tblemailcampaignLoadTop4`(p_UserID Int(11))
    SQL SECURITY INVOKER
BEGIN
SELECT EC.*,(Select MailChimpCampaignID from tblcampaignschedule where EC.CampaignID=tblcampaignschedule.CampaignID ) as MailChimpCampaignID,(Select  count(MailChimpCampaignID) from tblcampaignschedule) as msent,CT.CampaignType, (Select tblcampaignschedule.ScheduleDateTime from tblcampaignschedule 
where EC.CampaignID=tblcampaignschedule.CampaignID  order By ScheduleDateTime desc limit 1) as CampaignDateTime,
(Select Count(*) from tblemailsent ES
inner join tblcampaignschedule CS on CS.CampaignSceduleID= ES.CampaignSceduleID
where ES.Status= 'ProcessedMessage' and CS.CampaignID = EC.CampaignID)as Sent
FROM tblemailcampaign EC
inner join tblcampalgntype  CT on CT.CampaignTypeID= EC.CampaignTypeID
inner join tblcompanies C On EC.CompanyID = C.CompanyID
WHERE (p_UserID = 0 OR C.AddUserID = p_UserID)
Group by Sent
HAVING Count(Sent) > 0
 order by CampaignDateTime desc limit 4
;
END$$

DELIMITER ;