Jump to content
  • 0

Basic tallying in reports?


Question

Reporting: simply counting the number of times the choices in a drop down were selected? I have several drop downs on a form. I just need to tally the number of times each of the drop down choices was selected. Cant seem to just get a simple count of each dropdown choice.  In excel its a simple countif= fucntion.  Kinda surprised a robust system like caspio cant do it out of box.

Example: Form data page question asks people Whats Your Type of Music? Drop down options Rock, Jazz, Hip Hop, Classical. After 6 months I need to tally the number of people that chose Rock, Jazz, Hip Hop or Classical. It would be a very basic Excel countif= function.

Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0
On 10/14/2017 at 12:38 AM, roattw said:

Reporting: simply counting the number of times the choices in a drop down were selected? I have several drop downs on a form. I just need to tally the number of times each of the drop down choices was selected. Cant seem to just get a simple count of each dropdown choice.  In excel its a simple countif= fucntion.  Kinda surprised a robust system like caspio cant do it out of box.

Example: Form data page question asks people Whats Your Type of Music? Drop down options Rock, Jazz, Hip Hop, Classical. After 6 months I need to tally the number of people that chose Rock, Jazz, Hip Hop or Classical. It would be a very basic Excel countif= function.

I suppose that you may try using sql count function in a calculated field. E.g.:

select count(user_id) from table_name where type_of_music='Rock'

Link to post
Share on other sites
  • 0
On 10/24/2017 at 2:54 PM, roattw said:

Thanks Mathilda.  Will try it out and report back.  Would there be a slight alteration to that to not define each option but to instead count all occurrences of any chosen?

If  "type of music" field is included in your datasource, you may use the following syntax:

select count(user_id) from table_name where type_of_music=target.[@field:type_of_music]

 

target.[@field:type_of_music] - this part refers to the field value on report

Hope that helps

Link to post
Share on other sites
  • 0

A little closer, but it doesnt seem to tally correctly using

select count('submission_id') from Submissions_CME where cme_category=target.[@field:CME_Category] GROUP BY [@field:CME_Category]

I need to use a a COUNT () or COUNT (*) but CB hates that syntax. Also struggles with SQL WHERE statement.

Its odd because this works:

SELECT 
    COUNT(*)
FROM
    main_table

SELECT 
    COUNT(*)
FROM
    main_table

 But fails if I add WHERE qualifier:

SELECT 
    COUNT(*)
FROM
    main_table
WHERE
    type_of_rock = jazz;

 

I might try switching to getting some calculated field in the footer that would total counts of drop down selections:

Type of Music Tallies:

Rock = 10
Jazz = 2
Country = 4
Hip Hop = 1

 

Got really close with this but it wont tally, but does

<script type="text/javascript">
$('[@field:type_of_music]').change(function() {
    // get all selects
    var allSelects = $(type_of_music');

    // set values count by type
    var Rock = 0;
    var Jazz = 0;
    var HipHop = 0;
    
    // for each select increase count
    $.each(allSelects, function(i, s) {
        // increase count
        if($(s).val() == 'Rock') { Rock++; }
        if($(s).val() == 'Jazz') { Jazz++; }
        if($(s).val() == 'HipHop) {HipHop++; }
    });

    
    // update count values summary
    $('.cnt-Rock').text(Rock);
    $('.cnt-Jazz').text(Jazz);
    $('.cnt-HipHop').text(HipHop);
});
</script>

<div id="type_of_music">
    summary<br>
    Rock  = <span class="cnt-Rock">0</span> <br>
    Jazz  = <span class="cnt-Jazz">0</span> <br>
    HipHop  = <span class="cnt-HipHop">0</span> <br>
</div>

 

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...