select sum of distinct records

[ToonMariner]

I have a table of journeys..

 

journey_id sender_id recipient_id received distance

 

I need one query to select count of journeys, count of journeys where received is null and the sum of the distance...

 

this is what I have at the mo.

SELECT
COUNT(`journey`.`journey_id`) AS `sentNo` ,
COUNT(`t`.`journey_id`) AS `travelling` ,
SUM(`journey`.`distance`) AS `distance`
FROM
`journey`
LEFT JOIN
`users`
ON
`users`.`user_id` = `card_journey`.`sender`
LEFT JOIN
(SELECT * FROM `card_journey` WHERE `received` IS NULL) AS `t`
ON
`t`.`sender` = `users`.`user_id`
WHERE
`users`.`username` = :username

 

problem being of course that the sum of journeys sums up all values of the joins rather than teh distinct journeys themselves hence when the user has 2 journeys i get double the difference. 3 jurneys triple and so on...

 

Any pointers much appreciated

first to

 

[fenway]

That's because you need to unique-ify the journey first, then join that result.