запрос из двух таблиц с COUNT, SUM и GROUP BY

GP · 5 Дек 2009

запрос из двух таблиц с COUNT, SUM и GROUP BY

Есть 2 таблицы. В одной список переходов на сайт с указанием домена с которого выполнен переход. Каждая запись = одному переходу на сайт

PHP:

CREATE TABLE `visits` (
  `time` mediumint(9) NOT NULL default '0',
  `domain` varchar(32) collate utf8_bin NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `visits` VALUES(1, 'test.ru');
INSERT INTO `visits` VALUES(2, 'test.ru');
INSERT INTO `visits` VALUES(2, 'test2.ru');

В другой список заказов с указанием домена с которого перешел посетитель

PHP:

CREATE TABLE `checkout` (
  `sum` mediumint(9) NOT NULL default '0',
  `domain` varchar(32) collate utf8_bin NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `checkout ` VALUES(100, 'test.ru');
INSERT INTO `checkout ` VALUES(50, 'test.ru');

Нужно вывести таблицу вида:

PHP:

--------------------------------------------------------
|  Домен    |   Переходы  |   Заказы  |  Сумма заказов |
--------------------------------------------------------
|  test.ru  |   5         |   2       |  150           |
--------------------------------------------------------

Изначально я делал все через php скрипт с помощью 2х запросов, но теперь мне нужно объединить все в один чтобы была возможность сортировки по всем полям

Через php делал примерно так
SELECT domain, COUNT(*) FROM visits GROUP BY domain
и потом перебирая полученные данные(то есть список доменов и кол-во переходов с него) делал
SELECT COUNT(*), SUM(sum) FROM checkout WHERE domain='нужный домен'

А вот через один я понятия не имею как сделать. Пробовал через LEFT OUTER JOIN вот так:

SELECT
COUNT(*) AS visits,
`v`.domain AS domain,
SUM(`c`.sum) AS orders_sum
FROM `visits` AS `v`
LEFT OUTER JOIN checkout AS `c` ON (`v`.domain=`c`.domain)
GROUP BY domain

Но в результате склейки данные дублируются и кол-во с суммой вычисляются неправильно..
Как можно организовать правильный расчет значений в одном запросе? :confused:

-~{}~ 07.12.09 01:23:

Ответ:

PHP:

SELECT COUNT( * ) , `check`.`sum_check` , `visits`.`domain`
FROM `visits`
LEFT JOIN (
SELECT SUM( `checkout`.`sum` ) AS `sum_check` , `checkout`.`domain`
FROM `checkout`
GROUP BY `checkout`.`domain`
) AS `check` ON ( `check`.domain = `visits`.domain )
GROUP BY `visits`.`domain`

P.S. спасибо знакомому прогеру

запрос из двух таблиц с COUNT, SUM и GROUP BY

GP

Новичок