-1

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

mosSoft
  • 17
  • 6
  • please use ***parameterised queries*** - building SQL queries by concatenation etc. is a recipe for disaster. not only is it a source for many hard to debug syntax errors, it's also a wide, open gate for ***[SQL Injection attacks](https://bobby-tables.com/)***. – Franz Gleichmann Feb 09 '22 at 14:05
  • using (SQLiteConnection conn = new SQLiteConnection(con.dBasePath)) { SalesRecords.ItemsSource = conn.Query ("SELECT DISTINCT([soldProduct]) FROM [PURCHASED_PRODUCTS] " + "WHERE salesDate BETWEEN '" + btnDateFrom.Date.ToString("dd MMM yy") + "' AND '" + btnDateTo.Date.ToString("dd MMM yy") + "'"); } THIS CODE WORKS WITHOUT THE SUM([SOLDAMOUNT]), BUT I WANT TO POPULATE THE COLLECTIONVIEW WITH DISTINCT AND SUM AT SAMETIME – mosSoft Feb 09 '22 at 14:24

1 Answers1

0

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

ColeX
  • 14,062
  • 5
  • 43
  • 240
  • thank you very much, it work well following your process SalesRecords.ItemsSource = conn.Query("SELECT soldProduct, COUNT(soldProduct) AS count, " + "SUM(soldAmount) AS total FROM [PURCHASED_PRODUCTS] " + "WHERE salesDate BETWEEN '" + datFROM + "' AND '" + datTO + "' GROUP BY soldProduct"); – mosSoft Feb 10 '22 at 14:58