如何使用组合了Distinct、Sum和Count的Sql查询填充CollectionView..在Xamarin

Question

SQL查询样本

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代码

<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>

请问我如何才能做到这一点，同时使用distinct和sum

Answer 1

使用GROUP BY而不是DISTINCT。

DISTINCT用于选择不同的元素，仅此而已。如果要聚合(SUM(项，则需要使用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"

参考

如何组合SELECT DISTINCT和SUM((

具有不同和总和的SQL查询