Basic tallying in reports?

[roattw]

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.

[Mathilda]

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'

[roattw]

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?

[Mathilda]

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

[roattw]

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>