Yet, the following query:
SELECT d.name, disks.name, sum(free_space), sum(size) FROM devices d
LEFT OUTER JOIN disks ON (d.id=disks.computer_id AND disks.name='G:')
WHERE d.name='kensho';
Gives the following result:
kensho|G:|206140624896|237003182080
Which I can use to calculate the percentage outside of SQL. So...it's
actually the division that's causing the problem?
This query grabs the 2 sums separately, and the calculated percentage,
with odd results:
> SELECT d.name, sum(free_space), sum(size), sum(free_space)/sum(size)
FROM devices d LEFT OUTER JOIN disks ON (d.id =disks.computer_id AND
disks.name = 'G:') WHERE d.name='kensho';
> kensho|206140624896|237003182080|0
Does anyone know what's going on here? Is this just user error?
Ultimately, what I'm trying to do is grab only those devices records
that have free_space/size over a certain percentage. But to do that I
need to use a HAVING clause that tests SUM(free_space)/SUM(size)...which
always seems to be 0 when only one record is returned. Any help or
insight would be greatly appreciated.
Kareem Badr wrote:
This is probably my own SQL rustiness, but can someone explain this to
me?
If I run this query:
SELECT d.name, disks.name, free_space/size FROM devices d LEFT OUTER
JOIN disks ON (d.id=disks.computer_id AND disks.name='G:') WHERE
d.name='kensho' ;
I get the following result:
kensho|G:|0.869779988128673
If I run this query:
SELECT d.name, disks.name, SUM(free_space)/SUM(size) FROM devices d
LEFT OUTER JOIN disks ON(d.id=disks.computer_id AND disks.name='G:')
WHERE d.name='kensho' ;
I get the following result:
kensho|G:|0
The only difference between the 2 queries is that I am returning
SUM(free_space)/SUM(size) in the second one, istead of just
free_space/size.
Why is trying to sum the results when there is a single row resulting
in 0? Is there just some weird SQL syntax that I am forgetting?
