Member Avatar for 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

  • 2 Contributors
  • 1 Reply
  • 93 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by rch1231
Member Avatar for 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...

Edited by rch1231 because: missed table name
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.