15

I am currently using the following query to get some numbers:

SELECT gid, count(gid), (SELECT cou FROM size WHERE gid = infor.gid)       
FROM infor 
WHERE id==4325 
GROUP BY gid;

The output I am getting at my current stage is the following:

+----------+-----------------+---------------------------------------------------------------+
| gid      | count(gid)      | (SELECT gid FROM size WHERE gid=infor.gid)                    |
+----------+-----------------+---------------------------------------------------------------+
|       19 |               1 |                                                            19 | 
|       27 |               4 |                                                            27 | 
|      556 |               1 |                                                           556 | 
+----------+-----------------+---------------------------------------------------------------+

I am trying to calculate the weighted average i.e.

(1*19+4*27+1*556)/(19+27+556)

Is there a way to do this using a single query?

Legend
  • 113,822
  • 119
  • 272
  • 400

2 Answers2

17

Use:

SELECT SUM(x.num * x.gid) / SUM(x.cou)
  FROM (SELECT i.gid,
               COUNT(i.gid) AS num,
               s.cou
          FROM infor i
     LEFT JOIN SIZE s ON s.gid = i.gid
         WHERE i.id = 4325
      GROUP BY i.gid) x
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 1
    Awesome... Thank you very much for this. I was about to write a nested loop inside a procedure but then came across an article that said "If you require a nested loop, then you did not look at a JOIN" :) – Legend Oct 08 '10 at 02:32
  • 1
    @legend: The advice is correct, but JOINs also risk inflating records if there's more than one child associated to the parent. If you want distinct rows from the parent, it is better to use a subquery (EXISTS would be my recommendation). – OMG Ponies Oct 08 '10 at 02:35
  • I see. In my case, there's exactly one element but I will keep your advice in mind. – Legend Oct 08 '10 at 02:45
  • 1
    @Legend: Excellent - the better you know your data, the better your queries will be. – OMG Ponies Oct 08 '10 at 02:47
1

You could place your original query as a sub-query and SUM the records. I could not test this as I don't have the dataset you do, but it should work in theory ;)

SELECT SUM(gid)/SUM(weights) AS calculated_average FROM (
  SELECT gid, (COUNT(gid) * gid) AS weights
  FROM infor 
  WHERE id = 4325 
  GROUP BY gid);
Jason McCreary
  • 71,546
  • 23
  • 135
  • 174