Policz wiersze z określonym warunkiem w zapytaniu zbiorczym
Mam takie zapytanie, aby uzyskać liczbę PlayerSession
S z reconnect = TRUE
, pogrupowaną przez Player.country
:
SELECT
country,
COUNT(*) AS with_reconnect
FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id)
WHERE reconnect = TRUE
GROUP BY country
Chciałbym zmodyfikować go tak, aby pokazywał nie tylko liczbę ponownie podłączonych sesji, ale także liczbę całkowitą, coś w stylu:
SELECT
country,
COUNT(*) AS total,
(COUNT WHERE reconnect = TRUE) AS with_reconnect
FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id)
GROUP BY country
Czy jest to możliwe, a jeśli tak, to jaka jest właściwa składnia?
31
Author: Bart van Heukelom, 2012-02-22
4 answers
SELECT Country,
COUNT(*) AS Total,
COUNT(CASE WHEN Reconnect = true THEN 1 END) AS With_Reconnect
FROM PlayerSession S
LEFT JOIN Player P
ON P.id = S.player_id
GROUP BY country
67
Author: GarethD,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-22 12:35:15
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-22 12:35:15
Wystarczy
SELECT
p.country,
COUNT(*) AS total,
SUM(IF(s.reconnect=TRUE,1,0)) AS with_reconnect
FROM PlayerSession s
INNER JOIN Player p
ON p.id = s.player_id
GROUP BY p.country
Właśnie przepisałem zapytanie. Zawsze będziesz miał wiersz gracza dla każdego gracza, więc zmienił go na wewnętrzny JOIN. Również CONCAT nie był potrzebny, ponieważ zawsze będą wiersze PlayerSession w tym zapytaniu (chyba że nie ma żadnych Sesji) 18
Author: Simon at My School Portal,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-22 12:43:11
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-22 12:43:11
SELECT
country,
COUNT(CASE WHEN reconnect = TRUE THEN S.player_id ELSE NULL END) AS with_reconnect,
COUNY(*)
FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id)
GROUP BY country
1
Author: Lamak,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-22 12:36:42
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-22 12:36:42
SELECT
country,
COUNT(*) AS total,
sum(case when reconnect = TRUE then 1 else 0 end) AS with_reconnect
FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id)
GROUP BY country
0
Author: Vikram,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-22 12:35:46
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-22 12:35:46