CODEWITHSUNDEEP
mysqlinner-join
user1256132
user1256132

Reputation: 1

mysql self join

I have a table called receiving with 4 columns: 

id, date, volume, volume_units

The volume units are always stored as a value of either "Lbs" or "Gals".

I am trying to write an SQL query to get the sum of the volumes in Lbs and Gals for a specific date range. Something along the lines of: (which doesn't work)

SELECT sum(p1.volume) as lbs, 
p1.volume_units, 
sum(p2.volume) as gals, 
p2.volume_units 
FROM receiving as p1, receiving as p2
where p1.volume_units = 'Lbs'
and p2.volume_units = 'Gals' 
and p1.date between "2012-01-01" and "2012-03-07" 
and p2.date between "2012-01-01" and "2012-03-07"

When I run these queries separately the results are way off. I know the join is wrong here, but I don't know what I am doing wrong to fix it.

Upvotes: 0

Views: 318

Answers (5)

Shiva
Shiva

Reputation: 651

You can simply group by date and volume_units without self-join. 

SELECT date, volume_units, sum(volume) sum_vol
FROM receving
WHERE date between "2012-01-01" and "2012-03-07"
GROUP BY date, volume_units

Sample test:

select d, vol_units, sum(vol) sum_vol 
from
(
select 1 id, '2012-03-07' d, 1 vol, 'lbs' vol_units
union
select 2 id, '2012-03-07' d, 2 vol, 'Gals' vol_units
union
select 3 id, '2012-03-08' d, 1 vol, 'lbs' vol_units
union
select 4 id, '2012-03-08' d, 2 vol, 'Gals' vol_units
union
select 5 id, '2012-03-07' d, 10 vol, 'lbs' vol_units
) t
group by d, vol_units

Upvotes: 0

John Woo
John Woo

Reputation: 263943

This query will display the totals for each ID.

SELECT  s.`id`, 
        CONCAT(s.TotalLbsVolume, ' ', 'lbs') as TotalLBS,
        CONCAT(s.TotalGalVolume, ' ', 'gals') as TotalGAL
FROM
    (
        SELECT  `id`, SUM(`volume`) as TotalLbsVolume
        FROM    Receiving a INNER JOIN
                    (
                        SELECT  `id`, SUM(`volume`) as TotalGalVolume
                        FROM    Receiving
                        WHERE   (volume_units = 'Gals') AND
                                (`date` between '2012-01-01' and '2012-03-07')
                        GROUP BY `id`
                    ) b ON a.`id` = b.`id`
        WHERE   (volume_units = 'Lbs') AND
                (`date` between '2012-01-01' and '2012-03-07')
        GROUP BY `id`
    ) s

Upvotes: 1

lurscher
lurscher

Reputation: 26993

this is a cross join with no visible condition on the join, i don't think you meant that

if you want to sum quantities you don't need to join at all, just group as zerkms did

Upvotes: 0

zerkms
zerkms

Reputation: 255155

  SELECT SUM(volume) AS total_sum,
         volume_units
    FROM receiving
   WHERE `date` BETWEEN '2012-01-01'
                    AND '2012-03-07'
GROUP BY volume_units

Upvotes: 5

mathematical.coffee
mathematical.coffee

Reputation: 56955

You can achieve this in one query by using IF(condition,then,else) within the SUM:

SELECT SUM(IF(volume_units="Lbs",volume,0)) as lbs,
       SUM(IF(volume_units="Gals",volume,0)) as gals,
FROM receiving
WHERE `date` between "2012-01-01" and "2012-03-07"

This only adds volume if it is of the right unit.

Upvotes: 1

Related Questions