Re: [SQL] SUM() & GROUP BY

[Richard Huxton]

Martin Kuria wrote:
Here is what Division table contains:

SELECT * FROM ser_divisions;

divisions_name | divisions_id
---------------------------------------
DEE                   |   3131
...


Now I would like to get to know how each Division answered i.e.

SELECT s.pd_geo, COUNT(s.pd_geo)
FROM ser s
WHERE s.ser_divisions = '3131'
GROUP BY s.pd_geo;

divisions_name | pd_geo  | count
-----------------------------------------------
DEE                 |   1         |  9
DEE                 |   2         |  2
DEE                 |   3         |  6
DEE                 |   4         |  5
SELECT
  d.divisions_name,
  s.pd_geo,
  COUNT(s.pd_geo)
FROM
  ser_divisions d,
  ser s
WHERE
  d.divisions_id = s.ser_divisions
GROUP BY
  d.divisions_name, s.pd_geo
ORDER BY
  d.divisions_name, s.pd_geo
;
It's called a join, and any good SQL book should cover it.

--
  Richard Huxton
  Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings