Displaying information following the number of items

Monjoie Dominique asked on June 1, 2017 12:23

I use a repeater with custom query to get datas from two tables ('society' and 'activities'). For each society, I have a lot of activities and in this table, I have a field with 2 possible values. Following the amount of values 1 or 2, I have to display or not something in my transformation.

Is there away to count the amount of records with values 1 or values 2.

Select count (*) doesn't seem to work.

Thanks,

Recent Answers


Mariia Hrytsai answered on June 1, 2017 15:38

Could you please provide your query? I guess it should work but maybe with modification.

0 votesVote for this answer Mark as a Correct answer

Monjoie Dominique answered on June 1, 2017 16:16

Hello Mariia,

My custom query looks like Select custom_Cheese.NumWho, custom_Cheese.NameCheese, Custom_Cheese.CheeseStar from custom_Cheese WHERE ##WHERE## ORDERBY ##ORDERBY##.

The WHERE Clause is : NumWho = {%QueryString["Who"]#%} AND CheeseStar ='No'. I have to count the amount of CheeseStar = 'Yes' in another query.

My page is constructed with 3 repeaters : First one display general informations about cheese producer. The second one display a maximum of 3 cheeses with the status CheeseStar = 'Yes'. The last one display all the other cheeses from the producer. It is possible that one producer have no cheese with 'CheeseStar = Yes' so it changes the title for the other ones.

That's why I need to know if count (CheeseStar = 'Yes') is different from 0.

I tried to add a select count AS in my query but I always receive an error message so I can't use this count in my transformation.

I hope I'm clear enough because I'm not very strong in English,

In advance, Thanks.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on June 1, 2017 16:55 (last edited on June 2, 2017 01:43)

I guess the easy way to do it with UNION in 1 query: for example get 3 star rated cheeses and 5 non-rated cheeses:

 SELECT TOP 3 1 HasStar, custom_Cheese.NumWho, custom_Cheese.NameCheese, 
 Custom_Cheese.CheeseStar 
 FROM custom_Cheese 
 WHERE ##WHERE##  AND CheeseStar ='Yes'
 UNION
 SELECT TOP 5 0 HasStar, custom_Cheese.NumWho, custom_Cheese.NameCheese, Custom_Cheese.CheeseStar 
 FROM custom_Cheese 
 WHERE  ##WHERE##  AND CheeseStar ='No'
 ORDERBY HasStar DESC, ##ORDERBY##

Then you can process if with one repeater.

<script runat="server"> 
    string Title ="";

    protected override void OnInit(EventArgs e)
    {
        base.OnInit(e);
        if (Eval<bool>("HasStar") && !CMS.Helpers.RequestStockHelper.Contains("StarTitle")) {
                Title = "Cheeses With Star"
                CMS.Helpers.RequestStockHelper.Add("StarTitle", "Cheeses With Star")                
         }
        if (!Eval<bool>("HasStar") && !CMS.Helpers.RequestStockHelper.Contains("NoStarTitle")) {
                Title = "Cheeses With No Star"
                CMS.Helpers.RequestStockHelper.Add("NoStarTitle", Title)                
        }
    }
</script>
<%#Title%>
<%Eval("custom_Cheese.NameCheese")%><br>
0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.