MySQL COUNT()???

[Zotoff]

Пишу статистику для сайта. Хочу сделать отображение реферов. Нужно подсчитать сколько народу перешло от какого сайта. Т.е. каждый хит и хост фиксируется в БД. Нужно выдрать и подсчитать, сколько переходов с какого сайта. Делаю так

SELECT stat_hits.ref,COUNT(stat_hits.id) as hhits ,COUNT(stat_host.id) as hhost FROM stat_hits, stat_host GROUP BY stat_hits.ref, stat_hits.ref ORDER BY hhost DESC LIMIT 20

Понимаю, что наваритил кучу лишнего, но это все что я смог сварить =). Результаты дает явно неверные! В чем ошибка???

[Макс]

гораздо полезнее было бы показать дампы таблиц + пояснения к ним (что в каком поле)

[Zotoff]

stat_hits и stat_host имеют одну структуру

CREATE TABLE stat_host (
  id int(200) NOT NULL auto_increment,
  ip varchar(15) NOT NULL default \'\',
  agent varchar(100) NOT NULL default \'\',
  ref varchar(200) NOT NULL default \'\',
  date date NOT NULL default \'0000-00-00\',
  time time NOT NULL default \'00:00:00\',
  lang char(2) NOT NULL default \'\',
  PRIMARY KEY  (id)
)
ip, agent, lang, это для статистики. Пока не нужно, просто собираю, для данных. В поле ref пишется HTTP_REFERER. Зачем date и time отдельно, даже не спрашивайте, все равно не смогу объяснить =) т.к. разумного объяснения этому нет.

[Макс]

Что то вроде этого :
SELECT ref, COUNT(id) as referers FROM stat_host GROUP BY ref ORDER BY referers

[Zotoff]

SELECT ref, COUNT(id) as referers FROM stat_host GROUP BY ref ORDER BY referers
До этого допер сам по манам. Но проблема в другом. Как из 2-х таблиц выдрать, объединить и подсчитать? Т.е. из stat_host и stat_hits?

[Макс]

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

[Phoinix]

Макс
Насколько я понял, они должны быть не соединены, а объединены, связанных полей нет...

(SELECT ref, COUNT(id) as referers FROM stat_host GROUP BY ref)
UNION ALL
(SELECT ref, COUNT(id) as referers FROM stat_hits GROUP BY ref)
ORDER BY referers

Только группировки по обоим таблицам, не будет, поэтому будут совпадения по referers, но не более чем 2 совпадения для каждой записи.

Правда немного не понятно зачем объединять эти 2 таблицы, IMHO цифра получится неправильная, т.к. в хитах учитываются и хосты... Хотя может зависеть от реализации...
Иначе бы я советовал сделать для хитов и хостов одну таблицу такой же структуры, только добавить еще одно поле, в котором, ты и будешь указывать, хит это или хост

[Макс]

а может ref - общее поле ?
SELECT
t1.ref,
COUNT(t1.id) as hosts,
COUNT(t2.id) as hits
FROM
stat_host t1
LEFT JOIN stat_hits t2 USING(ref)
GROUP BY t1.ref

что-то вроде этого

[Zotoff]

Проел пост Phoinix и тут меня осинило.... Внатуре ведь в хиты пишутся и хосты, т.е. достаточно сделать выборку по хитам, т.е. то что и написал Макс в самом начале... Во блин =)))

SELECT
t1.ref,
COUNT(t1.id) as hosts,
COUNT(t2.id) as hits
FROM
stat_host t1
LEFT JOIN stat_hits t2 USING(ref)
GROUP BY t1.ref

Попробую, но до Left Join ман я пока не прочел.

[Zotoff]

А вот еще... Смотрите... делаю так...

SELECT ref, COUNT(id) as referers FROM stat_hits WHERE date>=\'$date\' AND ref NOT LIKE \'%soft.dsx.ru%\'  AND ref LIKE \'http://%\' GROUP BY ref ORDER BY referers DESC LIMIT 20

Т.е. выбираются все, что начинаются с http:// (это чтобы убрать пустые и заблоченные фаерами) и убераются все свои хиты (soft.dsx.ru). Остальное вроде понятно... Результат можете посмотреть на http://soft.dsx.ru/index.php?paga=stata Там такое...

 11. http://softik.net/index.php (18 переходов)   
 12. http://softik.net/newspage4.html (16 переходов)   
 13. http://softik.net/newspage5.html (16 переходов)

Как мне объядинить это, чтобы получилось типа
 11. http://softik.net/index.php (50 переходов)   

Или это только средствами php?

[Phoinix]

Zotoff
Заведи отдельное поле для доменов, и во время добавления данных в базу, добавляй только домен, а потом ориентируйся на него, иначе можно воспользоваться REGEXP вместо LIKE, правда скорость будет ниже...