Sum DISTINCT

[Alvaro Cobo]

Dear friends:

I have two tables. In one of those I store information about loans and
the year it was given. The other one stores the families which have
received this loans, with infromation abot the number of members in each
family (male and women).

Each family can receive more than one loan in a year.

The query I need to get is the total of male and women which have got a
loan during an especific year, making a join betwen this two tables.

La consulta que necesito obtener es: Suma de hombres y mujeres de las
familias que han participado de algún crédito durante cada año.

SELECT credito.anio,
Sum(familia.hombres) AS Total_Hombres,
Sum(familia.mujeres) AS Total_Mujeres
FROM credito
LEFT JOIN familia ON credito.id_familia = familia.id_familia
GROUP BY credito.anio;

+------+---------------+---------------+
| anio | Total_Hombres | Total_Mujeres |
+------+---------------+---------------+
| 2005 |            21 |            23 |
| 2006 |            11 |             9 |
+------+---------------+---------------+
(Translation: Hombre=Male; Mujeres=Women)

The problem is that as long as the families can receive more than one
loan in one year (so there are more than one row in the families/loan
row), the rows of the male and women get duplicated so the SUM of those
results it too big (for example, in the upper result table Male might be
12 instead of 21).

So is there a function like a DISTINCT to sum each different family in
this joined tables?.

Thanks a lot and best regards,

Alvaro Cobo

MySQL version 5.0.17
SO: Debian Sarge.

Tabla de ejemplo. Base de datos test.

CREATE TABLE `familia` (
  `id_familia` int(11) NOT NULL auto_increment,
  `nombre_familia` varchar(60) NOT NULL,
  `hombres` int(11) NOT NULL,
  `mujeres` int(11) NOT NULL,
  PRIMARY KEY  (`id_familia`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `familia` VALUES (1, 'Perez', 2, 3);
INSERT INTO `familia` VALUES (2, 'Suarez', 5, 3);
INSERT INTO `familia` VALUES (3, 'Sanchez', 6, 6);
INSERT INTO `familia` VALUES (4, 'Montalvo', 4, 5);
INSERT INTO `familia` VALUES (5, 'Cobo', 4, 3);
INSERT INTO `familia` VALUES (6, 'Larrea', 1, 3);


CREATE TABLE `credito` (
  `anio` year(4) NOT NULL,
  `id_credito` int(11) NOT NULL auto_increment,
  `id_familia` int(11) NOT NULL,
  PRIMARY KEY  (`id_credito`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

INSERT INTO `credito` VALUES (2005, 1, 1);
INSERT INTO `credito` VALUES (2005, 2, 1);
INSERT INTO `credito` VALUES (2005, 3, 1);
INSERT INTO `credito` VALUES (2005, 4, 3);
INSERT INTO `credito` VALUES (2005, 5, 4);
INSERT INTO `credito` VALUES (2005, 6, 2);
INSERT INTO `credito` VALUES (2006, 7, 2);
INSERT INTO `credito` VALUES (2006, 8, 3);