Два Group By

Насколько я понимаю, Group By в SQL запросе может быть только один. Так?

А есть ли способ получить в результате выполнения одного запроса две суммы, по двум колонкам?

Ситуация:
Есть таблица с Запросами (тема, дата и пр.), есть таблица с Ответами (текст, дата, статус и пр).
После объединения таблиц с помощью Join получаю такое

Запрос Ответ Статус ответа
Запрос1 Ответ1 принят
Запрос1 Ответ2 отклонен
Запрос1 Ответ3 принят
Запрос2 Ответ4 принят

Нужен результат

Запрос Всего ответов Принято
Запрос1 3 2
Запрос2 1 0

Сейчас я использую запрос с Group By такого вида

Это позволяет получить колонку с количеством принятых ответов.
А можно ли как-то сделать, чтобы была и колонка с принятыми, и колонка с общим количеством?

Я уже отработал вариант, когда выгружаю все данные о запросах и относящихся к ним ответам более простым запросом (без Group By)

А затем манипуляциями с массивами получаю нужный результат.

Проблема в том, что мне хотелось сортировать колонки с этими данными в таблице на сайте.
А для этого надо подставлять в ORDER BY название поля таблицы, сформированной SQL.
В результат массив нужной структуры и с нужными данными у меня есть, а сортировать по колонкам, для которых нет аналогичных колонок в MySQL не могу.

Вот я и пытаюсь понять, можно ли средствами SQL сгенерировать таблицу, в которой будет два поля с суммарным результатом.

Понимаю, что есть третий путь: загрузить данные на страницу, а дальше управлять ими с помощью javascript, я уже даже нашел jQuery-плагин tableSortable про это.

Но в javascript и jQuery я не силен, было бы здорово обойтись PHP и SQL. Если это, конечно, возможно.
Спасибо!

 

Спасибо! Сделал.

Вот ссылка на сконструированную БД на sqlfiddle.com http://sqlfiddle.com/#!2/ef6fb0

Повторю условия задачки.

Пользователи (таблица User) размещают запросы (Query) и дают на них ответы (Pitches). Для данной задачки не важно, но для общего понимания поясню: у пользователей есть две роли: "репортер" (rep) - размещает запросы; и "источник" (source) - отвечает на запросы.
Репортер выставляет полученным ответам (питчам) статусы (Pitches.pitch_status) принят (accepted), отклонен (rejected), или оставляет в полученных (submitted).

User и Query связаны по имейлу пользователя (User.email и Query.rep_email).
Query и Pitches связаны по параметру prefix (Query.prefix и Pitches.pitch_query_prefix). prefix - уникальный индекс, который генерится для запроса.
Pitches и Users связаны по имейлу пользователя (Pitches.pitch_source_email и User.email )

Действие происходит на странице пользователя-источника. Из сессии мы знаем его имейл, т.е. нам известен Pitches.pitch_source_email.

Задача 1.
Вывести одним запросом результат, в котором для запросов будет присутствовать суммрное количество поданых на него питчей со статусом "принят" ({pitches_accept_count} и суммарное количество всех полученных на него питчей({all_pitches_count}):

Query.subj | User.media_name | {pitches_accept_count} | {all_pitches_count}
Тема1 | Газета Вести | 3 | 10

pitches_accept_count и all_pitches_count должны быть получены в качестве столбцов в результирующей таблице, чтобы по ним можно было делать сортировку.
Вот как выглядит результат на вебе: https://www.dropbox.com/s/dt2hb9nopfukng0/%D0%A1%D0%BA%D1%8 ... 1.png?dl=0 (не увидел, как прицепить атач, поэтому ссылка на Дропбокс).

Можно посмотреть и вживую на http://feedcha.com/users/all-queries/ зайдя с тестовым логином bocharsky@yandex.ru и паролем 11111.

Задача 2
дополнить приведенный выше запрос параметром "статус моего питча".
pitch_status | Query.subj | User.media_name | {pitches_accept_count} | {all_pitches_count}

Т.е. если пользователь (мы знаем его имейл из сессии) подал ответ на данный запрос и сейчас у него статус submitted, вывести результат вида

submitted | Тема1 | Газета Вести | 3 | 10

Если пользователь не подал питч, результат будет
NULL | Тема1 | Газета Вести | 3 | 10

Вот. Огромное спасибо за помощь.

 

Прошу прощения. Первый опыт с sqlfiddle, еще не пристрелялся. Поправил

Вот новая ссылка на перегенеренную схему

http://sqlfiddle.com/#!2/e3969

 

вы меня натолкнули на мысль (наверное, недалекую, как мои знания, но рискну обозначить).

Погуглил тему "несколько count в одном запросе"
Нашел обсуждение с проблемой, похожу на мою http://webonrails.ru/post/480299561517060166/ : посчитать для пользователя суммарные значения разных параметров (количество публичных постов и постов всего).

Напомню, у меня задача: подсчитать для запроса количество принятых ответов и ответов всего.

Предлагается решение

Код (Text):

1. SELECT COUNT(*) as count, SUM(public='yes') as count_yes, SUM(public='no') as count_no FROM tbl WHERE user_id=1;

Составил по аналогии запрос для себя. В моем случае это выглядит так:

Код (Text):

1.  
2. SELECT SUM(pitch_status='accepted') AS pitch_accept_count, // количество ответов со статусом accepted
3.         COUNT(pitch_status) AS all_pitch_count, // количество ответов всего
4.         Query.id, Query.subj, Query.deadline, // кое-какие данные запроса
5.         User.media_name, // кое-какие данные пользователя
6.         Pitch.pitch_status,
7.         FROM Query LEFT OUTER JOIN Pitches ON prefix = pitch_query_prefix JOIN User ON Query.rep_email = User.email
8.                // объединяю Query и Pitches так, чтобы если у запроса нет ответов, запрос в выдаче был, а данные по Питчам были NULL  
9.                // таблицы Query и Pitches связаны по prefix (что-то вроде идентификатора запроса)
10.               // таблицы  Query и User связаны по имейлу пользователя (автора запроса)

Т.е. я хочу получить результат вида
Принято ответов (питчи) | Всего ответов | Тема запроса | Дедлайн запроса | Издание
3 | 10 | Почему коровы не летают? | 12.02.2015 | Вести

Запрос ожидаемо выдает ошибку синтаксиса.

Хочу спросить: это правильный путь для решения такой задачи?
Если да, то может подскажете, где проблемы с синтаксисом?

PS само объединение без внедрения новаций с SUM работает корректно. Т.е. запрос

Код (Text):

1. SELECT * FROM Query LEFT OUTER JOIN Pitches ON prefix = pitch_query_prefix JOIN User ON Query.rep_email = User.email

работает ОК

 

Да, но потом надо результат в один массив слить, чтобы выводить на вебе.
С этим хлопот не меньше, разве нет?
До сих пор я мыслил так: лучше уж помучиться с запросом, но потом получить данные в одном правильно организованном массиве, натравить на него foreach и все.

Ну и в моем случае идет речь о формировании таблицы результатов, в которой я хочу сделать на вебе сортировку по столбцам. Сортировку делаю на базе SQL запроса с помощью ORDER BY $sort по тем параметрам, которые есть в запросе. Например, захотел пользователь по названию СМИ - $sort = $media_name, захотел по количеству ответов $sort = $all_pitch_count.
ЕСли я не сделаю единый запрос, значит не могу реализовать сортировку.

 

Я уже сделал для этой задачи вариант, когда данные вытаскиваются "как умею", а потом переупаковываются PHP. Все окей, работает.

Проблема в том, что при такой реализации я не могу на сайте в таблице с данными сделать сортировку по столбцам. А я хочу)
Точнее, тогда надо делать эту сортировку тоже без SQL, на jquery, например. Я уже и плагины посмотрел типа tableSortable.

Но это снова дальняя дорога в неизведанное)
Вот и пытаюсь SQL-запрос забороть.

 

Рассказываю.
Для наглядного представления: страница на сайте с таблицей, которую надо сортировать, выглядит так https://www.dropbox.com/s/dt2hb9nopfukng0/%D0%A1%D0%BA%D1%8 ... 1.png?dl=0

Вот так я принимаю из веба параметры сортировки:

Код (Text):

1.  
2. // Сортировка
3.  
4. $key_array = array('my_pitches', 'subj', 'all_pitches', 'accepted_pitches', 'deadline', 'timestamp');
5. $sort_array = array('asc','desc');
6.  
7. if( isset($_GET['key']) )
8. {
9.     $key = $_GET['key'];
10.     $sort = $_GET['sort'];
11. }
12. else
13. {
14.     $key='deadline';
15.     $sort='asc';
16. }
17.  
18.  
19. // проверяем параметры, которые пришли из ссылки
20. // если они есть в массиве
21.  
22. if( in_array($key, $key_array) && in_array($sort, $sort_array) )
23. {
24.     // формируем строку в SELECT
25.     $query_order_str = $key . ' ' . $sort;
26. }
27. // если, например, пользователь подставил левые параметры
28. else
29. {
30.     // сортируем по умолчанию - по дедлайну asc
31.     $query_order_str = 'deadline';
32. }
33.  
34. if( $sort == 'asc' )
35. {
36.    $sort = 'desc';
37.    $vid_sort = 'sort_up';
38. }
39. else
40. {
41.    $sort = 'asc';
42.    $vid_sort = 'sort_down';  
43. }

На кнопках в заголовке таблицы висят ссылки вида

Код (Text):

1.  
2. <a href="?key=my_pitches&sort=<?php echo $sort; ?>
3. <a href="?key=all_pitches&sort=<?php echo $sort; ?>
4. <a href="?key=subj&sort=<?php echo $sort; ?>

Это стандартное решение, растиражированное по интернету

При клике по кнопке сортировки я получаю в $_GET[''] параметр по которому надо сортировать и направление сортировки.
И формирую из них переменную $query_order_str, которая будет подставляться в SQL запрос.

Например $query_order_str = subj desc

В SQL запросе это будет выглядеть как ORDER BY $query_order_str, т.е ORDER BY subj DESC

Теперь наверное понятно мое утверждение, что для того, чтобы сделать сортировку, мне надо чтобы используемые для сортировки параметры являлись столбцами в результирующей таблице SQL запроса.
Если в этой таблице будет subj или pitch_accept_count, то, соответственно, сортировка будет работать. Если нет - нет.

Если можете подсказать подход, как организовать сортировку таблицы, отталкиваясь не от SQL запроса, а просто от некоеко массива данных (который я соберу по результатам нескольких запросов), подскажите, буду признателен.

 

Именно

о том и речь. Поэтому я так и стремлюсь выгрузить все, что мне нужно одним запросом

а тут вы что имеете в виду?

 

О, спасибо! Да, результат верный. Клевый способ, не думал про такой подход.

Скажите, а "почистить и убрать дубли" - имеется в виду уже средствами php?

И правильно я понимаю, что этот способ не подходит, если нужно организовать сортировку на сайте с помощью ORDER BY $order в SQL запросе, как я описывал выше? Ведь чтобы сортировать результаты с помощью ORDER BY `параметр`надо чтобы запрос сразу формировал нужный результат, а не промежуточный, который надо еще приводить? Так?

Добавлено спустя 24 минуты 39 секунд:

Вау! То, что вы сделали - просто невероятно! ))

Погрузился в изучение CASE, IF и пр. Согласен с BaranPHP - узнал массу нового и полезного.
Осмысляю, гоняю запросы. Завтра на свежую голову поделюсь результатами на боевой эксплуатации.

Спасибо!

 

Выбрал для боевого вариант с COUNT(IF(`pitch_status`='accepted',1,NULL))
Работает отлично. Пока ничего не подвисает. Да думаю и не будет - посещаемость небольшая.

Огромное спасибо за то, что открыли такие вещи, как IF и CASE. Удивительное путешествие получилось! ))

Ну и напоследок: вдохновленный новыми знаниями попытался с их помощью решить свою вторую задачку: "если в списке запросов есть запрос, на который я сам дал ответ (source_pitch_emai = $my_email), то выводить его статус этого ответа в колонку my_pitch_status.

Сделал такое:

Код (Text):

1.  
2.         IF(source_pitch_email = '$my_email',
3.                 (CASE pitch_status
4.                     WHEN 'accepted' THEN accepted
5.                     WHEN 'submitted' THEN submitted
6.                     WHEN 'rejected' THEN rejected
7.                 END pitch_status), NULL)
8.                 AS my_pitch_status 

Как я рассуждал: берем IF и с его помощью проверяем, равно ли значение в поле pitch_status моему имейлу. IF здесь похож на тернарный оператор, нужны варианты для тру и фолс. Если условие выполняется, надо вытащить значение из поля pitch_status. Для этого я использую CASE. Его результат должен стать значением в случае TRUE. Если имейлы не равны, просто пишем NULL.

Но не работает))))

Наверняка вам очевидно, почему. Подскажите с чем бороться. Спасибо!

Вот полная версия запроса. Ругается на синтаксис:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'pitch_status), NULL) AS my_pitch_status FROM `Pitches`' at line 30

Код (Text):

1.  
2.  
3. SELECT
4. q.`subj`,
5. q.`deadline`,
6. u.`media_name`,
7. p.`pitches_accept_count`,
8. p.`all_pitches_count`,
9. p.`my_pitch_status`
10. FROM
11. `User` AS u
12.         INNER JOIN
13. `Query` AS q ON q.`rep_email`=u.`email`
14.         LEFT JOIN
15. (
16.     SELECT
17.         `pitch_query_prefix` AS `prefix`,
18.         COUNT(*) AS `all_pitches_count`,
19.         COUNT(IF(`pitch_status`='accepted', 1, NULL)) AS `pitches_accept_count`,
20.  
21.         IF(source_pitch_email = 'bocharsky@yandex.ru',
22.                 (CASE pitch_status
23.                     WHEN 'accepted' THEN accepted
24.                     WHEN 'submitted' THEN submitted
25.                     WHEN 'rejected' THEN rejected
26.                 END pitch_status), NULL)
27.                 AS my_pitch_status                     
28.        
29.     FROM `Pitches`
30.     GROUP BY `pitch_query_prefix`
31. ) AS p ON p.`prefix`=q.`prefix` WHERE q.deadline > NOW()"

И еще раз огромное спасибо за уже имеющиеся результаты!