0

I have a data table that looks like this:

Landing Page Keyword Sessions
Landing Page 1 Keyword 1 26
Landing Page 1 Keyword 2 4
Landing Page 2 Keyword 1 6

There are multiple Landing Pages and Keywords. Both are dimensions and text fields. Sessions is the amount for each of those dimensional values.

I'm trying to see the top 15 Landing Pages measured by descending sum of Sessions and the top 15 Keywords measured by descending sum of Sessions just for those 15 Landing Pages.

In my query, I'm trying to group on Landing Pages and Keywords. I believe this can be done with a subquery but I'm having trouble writing it.

Query #1 looks like this:

SELECT TOP 15 Table1.[Landing Page], Sum(Table1.Sessions) AS SumOfSessions
FROM Table1
GROUP BY Table1.[Landing Page]
ORDER BY Sum(Table1.Sessions) DESC;

Query #2 looks like this:

SELECT Query1.[Landing Page], Query1.SumOfSessions, Table1.Keyword, Sum(Table1.Sessions) AS SumOfSessions1
FROM Query1 INNER JOIN Table1 ON Query1.[Landing Page] = Table1.[Landing Page]
GROUP BY Query1.[Landing Page], Query1.SumOfSessions, Table1.Keyword
ORDER BY Sum(Table1.Sessions) DESC;

It seems like I just need some help adding a subquery to Query #2 that selects only the top 15 Keywords for those top 15 Landing Pages. Inner joining to the first query provides the top 15 Landing Pages.

Based on my example data and considering only top 1 for both Landing Page and Keyword, I expect the results to look like this:

Landing Page Keyword Sessions
Landing Page 1 Keyword 1 26
Landing Page 2 Keyword 1 6
Yves Gurcan
  • 1,096
  • 1
  • 10
  • 25
mrmatt11
  • 77
  • 2
  • 10
  • Edit question to show sample data and desired output as text tables. – June7 Feb 01 '21 at 19:41
  • @June7 done. Thanks! – mrmatt11 Feb 02 '21 at 00:50
  • 1
    Not much of a data sample. I doubt Keywords all start with "Keyword". Appears to simply pull TOP 1 Sessions for each LandingPage. Involves a nested query. https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access or maybe https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – June7 Feb 02 '21 at 02:10

0 Answers0