Что не так с SQL запросом в GOOGLE BIGQUERY?

Question

[Oleganalytics]

Друзья, помогите решить проблему.
Пытаюсь построить воронку по URL, запрос выдает следующее: "Error: Cannot group by an aggregate."
Данные беру из Google Analytics.
Вот сам запрос:
SELECT
COUNT(DISTINCT s1.clientId ) AS s1.User,
s1.page.pagePath
FROM (
SELECT
COUNT(DISTINCT clientId ) as User,
page.pagePath
FROM
[89437389479847487]
GROUP BY user,page.pagePath
HAVING
page.pagePath = 'URL 1'
AND page.pagePath != 'utm'
) AS s1
INNER JOIN (
SELECT
COUNT(DISTINCT clientId ) AS User,
page.pagePath
FROM
[89437389479847487]
GROUP BY user,page.pagePath
HAVING
page.pagePath = 'URL 2'
AND page.pagePath != 'utm') AS s2
ON
s1.User=s2.User
GROUP BY s1.user,s1.page.pagePath
Очень буду признателен за ответ.

Answer 1

[Boodmoo]

Чтоб работало по идее нужно выкинуть из вложенных select`ов группировку по user. Ну и обилие DISTINCT это плохо. Как минимум можно убрать DISTINCT из первого SELECT`а мне так кажется

SELECT COUNT(s1.clientId ) AS s1.User, s1.page.pagePath
FROM (
	SELECT
	COUNT(DISTINCT clientId ) as User,
	page.pagePath
	FROM
	[89437389479847487]
	GROUP BY page.pagePath
	HAVING
	page.pagePath = 'URL 1'
	AND page.pagePath != 'utm'
	) AS s1
INNER JOIN (SELECT COUNT(DISTINCT clientId ) AS User, page.pagePath
			FROM
			[89437389479847487]
			GROUP BY page.pagePath
			HAVING page.pagePath = 'URL 2' AND page.pagePath != 'utm') AS s2
ON  s1.User=s2.User
GROUP BY s1.user,s1.page.pagePath

Comments

[Oleganalytics]

В итоге вот так заработало:
SELECT
s1.page,
COUNT(s1.clientId ) AS step1,
COUNT(s2.clientId ) AS step2
FROM (
SELECT
page.pagePath AS page,
clientId
FROM
[limetest.limetableempty]
WHERE
page.pagePath CONTAINS 'url'
AND page.pagePath != 'utm_'
GROUP BY 1, 2
) s1
LEFT JOIN (
SELECT
clientId
FROM
[limetest.limetableempty]
WHERE
page.pagePath CONTAINS 'url'
AND page.pagePath != 'utm_'
GROUP BY 1
) s2
ON s1.clientId = s2.clientId
GROUP BY s1.page