[sql] join multiple tables and sum

CREATE TABLE IF NOT EXISTS `b` (
  `id_b` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  PRIMARY KEY (`id_b`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=9 ;


INSERT INTO `b` (`id_b`, `p_id`, `a`) VALUES
(1, 1, 100),
(2, 1, 101),
(3, 2, 200),
(4, 2, 201),
(5, 3, 300),
(6, 3, 301),
(7, 4, 400),
(8, 4, 401);


CREATE TABLE IF NOT EXISTS `c` (
  `id_b` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  PRIMARY KEY (`id_b`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=9 ;



INSERT INTO `c` (`id_b`, `p_id`, `a`) VALUES
(1, 1, 100),
(2, 1, 101),
(3, 2, 200),
(4, 2, 201),
(5, 3, 300),
(6, 3, 301),
(7, 4, 400),
(8, 4, 401);



CREATE TABLE IF NOT EXISTS `p` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=7 ;



INSERT INTO `p` (`id`, `client`) VALUES
(1, 'иванов'),
(2, 'петров'),
(3, 'сидоров'),
(4, 'разин');

SELECT *,sum(b.a),sum(c.a) FROM `p`
left join b on (p.id=b.p_id)
left join c on (p.id=c.p_id)
group by(id)

SELECT 
    * , 
    sum( DISTINCT b.a ) , 
    sum( DISTINCT c.a )
FROM 
    `p`
        LEFT JOIN 
    `b` 
        ON ( p.id = b.p_id )
        LEFT JOIN 
    `c` 
        ON ( p.id = c.p_id )
GROUP BY (id)

INSERT INTO `c` (`id_b`, `p_id`, `a`) VALUES
(1, 1, 100),
(2, 1, 100),
(3, 2, 200),
(4, 2, 200),
(5, 3, 300),
(6, 3, 300),
(7, 4, 400),
(8, 4, 400);

SELECT 
    * , 
    sum(b.a )/count(DISTINCT b.id_b) , 
    sum(c.a )/count(DISTINCT c.id_b)
FROM 
    `p`
        LEFT JOIN 
    `b` 
        ON ( p.id = b.p_id )
        LEFT JOIN 
    `c` 
        ON ( p.id = c.p_id )
GROUP BY (id)

...
    sum(b.a )/count(DISTINCT b.id_b) , 
...

SELECT p.client client ,sum(b.a) sum FROM b,p where p.id=b.p_id group by p.client
union all
SELECT p.client client ,sum(c.a) sum FROM c,p where p.id=c.p_id group by p.client
order by client

select 
   p.client client
  ,sum(e.a) sum
from 
  (SELECT p_id, a FROM b
    union all
   SELECT p_id, a FROM c) e,
  p
where
  p.id=e.p_id
group by p.client
order by client

select 
   p.client client
  ,bp.sum   sumb
  ,cp.sum   sumc
from 
  (SELECT p_id, sum(b.a) sum FROM b group by p_id) bp,
  (SELECT p_id, sum(c.a) sum FROM c group by p_id) cp,
  p
where
  p.id=bp.p_id
  and
  p.id=cp.p_id
order by client