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

• • Reply
  

  mmazeemahmad

  Member

  628 Points

  385 Posts

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

  Nov 14, 2011 08:50 AM|LINK

  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

  • Edited by mmazeemahmad on Nov 14, 2011 08:52 AM
  • Edited by mmazeemahmad on Nov 14, 2011 12:10 PM
  • Edited by mmazeemahmad on Nov 14, 2011 12:10 PM
  • Edited by mmazeemahmad on Nov 14, 2011 12:10 PM
• • Reply
  

  mmazeemahmad

  Member

  628 Points

  385 Posts

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

  Nov 14, 2011 11:51 AM|LINK

  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???

  • Edited by mmazeemahmad on Nov 14, 2011 12:10 PM
• • Reply
  

  mmazeemahmad

  Member

  628 Points

  385 Posts

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

  Nov 14, 2011 12:11 PM|LINK

  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 ;

  • Marked as answer by mmazeemahmad on Nov 14, 2011 12:35 PM