I have a dataset about trains, it's including a table for the customers information which is a number representing an age group and the amount of travellers for that age group.
The ID represents a location which has multiple departure times, which has multiple age groups.
The data looks something like this
| StationID | Time of Departure | TravellerID | Amount of travellers |
|---|---|---|---|
| 1 | 12:13 | 4001 | 30 |
| 1 | 12:13 | 4002 | 15 |
| 1 | 19:45 | 4001 | 10 |
| 1 | 19:45 | 4002 | 20 |
I want to sum the amount of travellers for each departure I tried to code it this way:
SELECT StationID,[Time of Departure], sum(Amount)
FROM Train_Stations AS TS
INNER JOIN DepartureData AS DD
ON DD.FK_StationID = TS.PK_StationID
INNER JOIN CustomerInfo AS CI
ON CI.FK_StationID = TS.PK_StationID
GROUP BY StationID, [Time of Departure]
The result is like this:
| StationID | Time of Departure | Amount |
|---|---|---|
| 1 | 12:13 | 75 |
| 1 | 12:13 | 75 |
| 1 | 19:45 | 75 |
| 1 | 19:45 | 75 |
But I want it like this:
| StationID | Time of Departure | Amount |
|---|---|---|
| 1 | 12:13 | 45 |
| 1 | 19:45 | 30 |
CodePudding user response：
Seems, you do something different.Based on your data query is correct
WITH CTE(StationID,DEPARTURE_TIME,TRAVELLERID,AMOUNT_OF_TRAVELLERS) AS
(
SELECT 1,CAST('12:13'AS TIME),4001,30 UNION ALL
SELECT 1,CAST('12:13'AS TIME),4002,15 UNION ALL
SELECT 1,CAST('19:45'AS TIME),4001,10 UNION ALL
SELECT 1,CAST('19:45'AS TIME),4002,20
)
SELECT C.StationID,C.DEPARTURE_TIME,SUM(AMOUNT_OF_TRAVELLERS)TOTAL_TRAVELLERS
FROM CTE AS C
GROUP BY C.StationID,C.DEPARTURE_TIME
CodePudding user response：
You should specify the column as DD.StationID. It will return as an expected result.
SELECT DD.StationID,DD.[Time of Departure], sum(DD.Amount)
FROM Train_Stations AS TS
INNER JOIN DepartureData AS DD
ON DD.FK_StationID = TS.PK_StationID
INNER JOIN CustomerInfo AS CI
ON CI.FK_StationID = TS.PK_StationID
GROUP BY DD.StationID, DD.[Time of Departure]