How to Populate CollectionView with Sql Query with combination of Distinct, Sum and Count .... in Xa-CodePudding

SQL QUERY SAMPLE

using (SQLiteConnection conn = new SQLiteConnection(con.dBasePath))
                {
                    SalesRecords.ItemsSource = conn.Query<DATA_BINDING.PURCHASED_PRODUCTS> 
                    ("SELECT DISTINCT([soldProduct]), (SUM ([soldAmount])) FROM 
                    [PURCHASED_PRODUCTS] "  
                    "WHERE salesDate BETWEEN '"   btnDateFrom.Date.ToString("dd MMM yy") 
                      "' AND '"   btnDateTo.Date.ToString("dd MMM yy")   "'");

                }]

.....XAMARIN CODE.....

     <Label **Text="{Binding soldProduct}"** HorizontalOptions="Start" Margin="8,0,0,0" FontSize="14" FontAttributes="Bold" TextColor="White"></Label>


<Label **Text="{Binding soldAmount}"** TextColor="#2ABD8F" HorizontalOptions="CenterAndExpand" FontSize="22" HorizontalTextAlignment="Center"  FontAttributes="Bold" VerticalOptions="CenterAndExpand"
                                                           LineBreakMode="TailTruncation" Margin="0,-11,0,0"></Label>

Please how can i make this possible, with the distinct and sum at same time

CodePudding user response：

Use GROUP BY instead of DISTINCT.

DISTINCT is used to select distinct elements , nothing more . If you want to aggregate(SUM) the items you need to use GROUP BY.

"SELECT soldProduct, (SUM ([soldAmount])) FROM [PURCHASED_PRODUCTS] "  
 "WHERE salesDate BETWEEN '"   btnDateFrom.Date.ToString("dd MMM yy") 
  "' AND '"   btnDateTo.Date.ToString("dd MMM yy")   "'"   "GROUP BY soldProduct"

Refer to

How to combine SELECT DISTINCT and SUM()

SQL query with distinct and sum