Join two queries

[rikz]

Hi!
I need help with cobining two queries into one, if possible.

The queries looks like this:

SELECT route, SUM(volume) AS rested_volume FROM buff WHERE route = 019 AND date = '2010-10-10';

and...

SELECT id.RouteNo, SUM(id.PickVol) AS total_volume
FROM imported_data id 
WHERE id.id_date = '2010-10-10'
AND id.LoggCode = 40
AND RouteNo = '019';

I've tried this:

SELECT id.RouteNo, SUM(id.PickVol) AS total_volume, SUM(ROUND(b.volume/1000,2)) AS rested_volume
FROM imported_data id 
LEFT JOIN buff b ON id.RouteNo = b.route AND id.id_date = b.date
WHERE id.id_date = '2010-10-10'
AND id.LoggCode = 40
AND RouteNo = '019'
GROUP BY RouteNo;

...but it gives duplicate values. I thinks something is wrong with the join condition but I don't know what. Any ideas?

/rikz

[rch1231]

The trick is to get the basic query with all of the correct data showing first then add in the SUM options afterward. Try this:

SELECT id.RouteNo, id.PickVol AS total_volume, b.volume AS rested_volume
FROM imported_data id 
LEFT JOIN buff b ON id.RouteNo = b.route AND 
WHERE id.id_date = '2010-10-10'
AND id.LoggCode = 40
AND RouteNo = '019'
ORDER BY RouteNo, id.id_date  ;

Once that works select and sum the result data:

select data1.RouteNo, SUM(data1.total_volume), SUM(data1.rested_volume) from
(SELECT id.RouteNo, id.PickVol AS total_volume, b.volume AS rested_volume
FROM imported_data id 
LEFT JOIN buff b ON b.route  = id.RouteNo AND b.DATE = id.id_date
WHERE id.id_date = '2010-10-10'
AND id.LoggCode = 40
AND RouteNo = '019'
ORDER BY RouteNo, id.id_date ) as data1
Group By data1.RouteNo

See if that works...