How to display aggregate function results in radgrid

[york]

Hi,

I have a table named Orders with a field "ChildFirstName". I like to get the total count of each ChildFirstNameand its percentage against total number of table. I create the query for that is

SelectCommand="SELECT [ChildFirstName], [COUNT(ChildFirstName) AS QuantityOrdered], [(QuantityOrdered * 100 / (SELECT COUNT(*) FROM Orders)) AS Percentage] FROM [Orders] GROUP BY [ChildFirstName] ORDER BY [ChildFirstName] DESC"

I create radgrid to display it,

                    <Columns>
                           <telerik:GridBoundColumn DataField="ChildFirstName" HeaderText="Child's First Name"
                            SortExpression="ChildFirstName" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="ChildFirstName">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="QuantityOrdered" HeaderText="Quantity Ordered"
                            SortExpression="QuantityOrdered" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="QuantityOrdered">
                        </telerik:GridBoundColumn>
                        <telerik:GridBoundColumn DataField="Percentage" HeaderText="Percentage"
                            SortExpression="Percentage" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true" UniqueName="Percentage">
                        </telerik:GridBoundColumn>
                    </Columns>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:freepersonalizedConnectionString %>"
            SelectCommand="SELECT [ChildFirstName], [COUNT(ChildFirstName) AS QuantityOrdered], [(QuantityOrdered * 100 / (SELECT COUNT(*) FROM Orders)) AS Percentage] FROM [Orders] GROUP BY [ChildFirstName] ORDER BY [ChildFirstName] DESC">
        </asp:SqlDataSource>

But it gives error:
Invalid column name 'COUNT(ChildFirstName) AS QuantityOrdered'.
Invalid column name '(QuantityOrdered * 100 / (SELECT COUNT(*) FROM Orders)) AS Percentage'.

How to do it in right? Thanks.

[Marin]

Hello,

 You should not specify group clause and aggregate functions in the select command of the grid. If you need to have a separate column that shows some calculated and aggregated data you can use the GridCalcualtedColumn (more information can be found in the related section in this help topic) or show footer aggregates as shown in this demo.

Best wishes,
Marin
the Telerik team

[york]

Hi Marin,
In this case, I have to use count function to get the number of childFirstName and group together. I have solved this problem simply by removing brackets and parenthesis in the select clause, which is very surprising.

One more question:
I want to get different fields based on OrderDate, but I can not add OrderDate like:

SelectCommand="SELECT OrderDate, ChildFirstName, COUNT(ChildFirstName) AS QuantityOrdered, COUNT(ChildFirstName)*100/ 
                                (SELECT COUNT(*) FROM Orders) AS Percentage 
                                FROM Orders  
                                GROUP BY OrderDate,ChildFirstName 
                                ORDER BY ChildFirstName"

Because it gives:
6/14/11 6:02:53 PM    
Alice    
1  
3.13 %


6/14/11 8:35:35 AM
Bill
1
3.13 %


7/20/11 11:53:40 AM
Dale
1
3.13 %


6/14/11 1:55:23 AM
Dan
1
3.13 %


7/14/11 7:35:17 PM
Darrell
1
3.13 %


7/14/11 7:39:35 PM
Darrell
1
3.13 %


6/28/11 11:01:16 PM
Deby
1
3.13 %


7/20/11 12:08:52 PM
Fred
1
3.13 % 
 

Everything is 1. Same ChildFirstName is not grouped together. So I have to bind datasource myself. More specifically, there are 2 raddatepicker, and I want to run select command when 2 raddatepickers are set, such as:

SelectCommand="SELECT ChildFirstName, OrderDate from Orders AS NameByDate WHERE OrderDate >= raddatepicker1 AND OrderDate <=raddatepicker2


SelectCommand="SELECT ChildFirstName, COUNT(ChildFirstName) AS QuantityOrdered, COUNT(ChildFirstName)*100/ (SELECT COUNT(*) FROM NameByDate) AS Percentage FROM Orders GROUP BY ChildFirstName ORDER BY ChildFirstName"


And then bind datasource to radgrid, and update masterviewtable.

How to do it? Thanks.

[Marin]

Hello York,

 The grid does not expect data containing different SQL groups to be passed as its datasource. If you need to show grouped data you can use the built-in grouping capabilities of RadGrid as shown in this demo. Additionally if you need to apply more complex manipulation on the data before binding you use the server side NeedDataSource event.

Kind regards,
Marin
the Telerik team