1. 

   rbecker, Member

   I have a report that uses a multiselect option to retrieve some input from my users. For each option they have selected I would like to run a count(*) on it and return the result. Right now it will perform the count for every single option together and display it as one result, but I would like one result per option selected, and with a total row if possible also. Is this able to be done? I looked through the documentation but it doesn't look like myDBR has any built in for loops and MySQL is only able to do it via a cursor.

   My report code is:
   CREATE PROCEDURE `sp_DBR_LeadInsertionCounts`(vStartDate datetime, vEndDate datetime, inLists text) BEGIN

   SELECT count(*) AS 'Lead Count' FROM asterisk.vicidial_list WHERE find_in_set(list_id, inLists) AND entry_date BETWEEN vStartDate and vEndDate;

   END

   And the procedure that generates the options is:
   CREATE PROCEDURE `sp_DBR_SelectLists`(inLists varchar(255)) BEGIN

   select list_id, concat(list_id, " - ", list_name) from asterisk.vicidial_lists where list_id like concat('%', inLists, '%') limit 40;

   END
2. 

   myDBR Team, Key Master

   Usually you try to avoid loops in SQL whenever possible. If you want to have a lead count per list_id (or with some textual explanation) you couild do following:

   SELECT 'dbr.sum', 'Lead Count';
   
   SELECT list_id, count(*) AS 'Lead Count'
   
   FROM asterisk.vicidial_list
   
   WHERE find_in_set(list_id, inLists) AND entry_date BETWEEN vStartDate and vEndDate
   
   GROUP BY list_id;

   Is that somethihg you were looking for?

   --
   myDBR Team
3. 

   rbecker, Member

   Yes, that was exactly what I was looking for. I made a few modifications to make it display the data in a better format but that did the trick. Thank you!

Reply

You must log in to post.