sqlite - distinct sum 不区分值

Question

我有 2 张 table 、预订和文章:

预订

------------------------------
Id     |   Name   |     City |
------------------------------
 1     |  Mike    | Stockholm
 2     |  Daniel  | Gothenburg
 2     |  Daniel  | Gothenburg
 3     |  Andre   | Gothenburg (Majorna)

文章

-------------------------------------------------------------
ArticleId    |    Name       |    Amount |    ReservationId | 
-------------------------------------------------------------
10           |   Coconuts    |    1      |    1         
10           |   Coconuts    |    4      |    2     
11           |   Apples      |    2      |    2
12           |   Oranges     |    2      |    3

我想选择 Articles Name 和 Articles.Amount per Articles.ArticleId 和 Reservations.City 的总和。

我的代码:

SELECT distinct r.ID,a.Name as ArticleName,
       sum(a.Amount) as ArticlesAmount,
       substr(r.City,1,3) as ToCityName 
FROM Reservations r 
INNER JOIN Articles a 
      on r.Id = a.ReservationId 
WHERE  a.Name <> '' 
GROUP BY ToCityName,a.ArticleId,a.Name 
ORDER BY ToCityName ASC

这给了我以下结果:

Id | ArticleName | ArticlesAmount | ToCityName

2  |  Coconuts   |   8            |   Got 
2  |  Apples     |   4            |   Got 
3  |  Oranges    |   2            |   Got 
1  |  Coconuts   |   1            |   Sto 

但我想要:

Id | ArticleName | ArticlesAmount | ToCityName

2  |  Coconuts   |   4            |   Got 
2  |  Apples     |   2            |   Got 
3  |  Oranges    |   2            |   Got 
1  |  Coconuts   |   1            |   Sto 

帮助将不胜感激，请解释一下:)

Fiddle

Answer 1

看看SQLFiddle

代码:

SELECT distinct r.ID,a.Name as ArticleName,
       sum(distinct a.Amount) as ArticlesAmount,
       substr(r.City,1,3) as ToCityName 
FROM Reservations r 
INNER JOIN Articles a 
      on r.Id = a.ReservationId 
WHERE  a.Name <> '' 
GROUP BY ToCityName,a.ArticleId,a.Name 
ORDER BY ToCityName ASC

您希望确保按每个组出现的不同次数对金额求和。