Basic grouping within a window function

I am working with window functions for the first time. I have a basic window function, I'd like to group the results by Medium, when I do this I get the error:

Error: SELECT list expression references totals.visits which is neither grouped nor aggregated at [1:12]

To my mind, I have summed totals.visits on the first row, what am I missing here? I would like to see the total number of visits broken down by country, for example:

VISITS          COUNTRY
1500            United Kingdom
750             Ireland
etc.

Here is my query:

 SELECT
        SUM(totals.visits) OVER(PARTITION BY geoNetwork.country
                                ORDER BY geoNetwork.country) AS Visits_by_Medium,
        trafficSource.medium AS Medium
    FROM `xxx.ga_sessions_20171010`
    GROUP BY Medium

1 answer

  • answered 2017-10-11 10:06 Tim Biegeleisen

    I think you just want a simple GROUP BY query:

    SELECT
        SUM(totals.visits) AS VISITS,
        geoNetwork.country
    FROM xxx.ga_sessions_20171010
    GROUP BY
        geoNetwork.country;
    

    Edit:

    Run the following query to see what sum as a window function will return you:

    SELECT
        SUM(totals.visits) OVER(PARTITION BY geoNetwork.country
                                ORDER BY geoNetwork.country) AS Visits_by_Medium,
        trafficSource.medium AS Medium
    FROM xxx.ga_sessions_20171010;