mercredi 29 avril 2015

distinct sum does not distinct values

I have 2 tables, reservations and articles:

Reservations

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

Articles

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

I want to select Articles Name and the sum of Articles.Amount per Articles.ArticleId and Reservations.City.

My code:

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

This gives me following result:

Id | ArticleName | ArticlesAmount | ToCityName

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

But i want:

Id | ArticleName | ArticlesAmount | ToCityName

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

Help would be appreciated, and an explanation please :)

Fiddle

Aucun commentaire:

Enregistrer un commentaire